Grid report: revenue per customer per month

[Stefan]

I would like to create a grid report that shows revenue per customer per month. But only if this amount (revenue per month) exceeds a certain value. I thought I could do this with the ‘Having group’ function, but that doesn't seem to work. (Without the ‘Having group’ the report works fine).

I tried it like this:
Group by: MONTH:date, customer
Columns: sum:revenue

Filter:
Having: sum:revenue > 100

Is there a way to generate a corresponding report?

[yuri]

I tried such a report and it worked for me.

[Stefan]

Hm...strange yuri

I made a mistake in my first post, the numbers are negative, but that shouldn't really make a difference in my opinion (Less than instead of Greater than). Here is the report without having filter. As you can see, there are many fields that are less than 200.



If I add this having filter to the report:



I don't get a single result. And I don't know why.


Here are also the parameters I use:

[yuri]

If you have multiple currencies, you need to use the Converted field in the filter.

[Stefan]

yuri: We don't actually have multiple currencies. But I also tried it with the converted field. No difference - no result.

[yuri]

Could not reproduce. I don't know what it could be.

Here's the SQL. Looks fine.

SELECT
  DATE_FORMAT(cTest.date, '%Y-%m') AS `MONTH:date`,
  cTest.account_id AS `accountId`,
  SUM(
    (
      cTest.amount * amountCurrencyRate.rate
    )
  ) AS `SUM:amountConverted`
FROM
  `c_test` AS `cTest`
  LEFT JOIN `account` AS `account` ON cTest.account_id = account.id
  LEFT JOIN `currency` AS `amountCurrencyRate` ON amountCurrencyRate.id = cTest.amount_currency
WHERE
  DATE_FORMAT(cTest.date, '%Y-%m') IS NOT NULL
  AND cTest.deleted = 0
GROUP BY
  DATE_FORMAT(cTest.date, '%Y-%m'),
  cTest.account_id
HAVING
  SUM(
    (
      cTest.amount * amountCurrencyRate.rate
    )
  ) < -50
​

Hope somebody from our support will be able to reproduce.

[yuri]

On your screenshot I see only one grater than -200. 0 does not count, as it is substituted for empty cells.

[Stefan]

yuri Did you create that SQL command by hand or is this created by the report and I can also view it somewhere as debug-output or something to see what it actually looks like for me.

P.s. I use "less than" instead of "greater than", so there should be several results.

[yuri]

https://docs.espocrm.com/administration/log/#sql-queries

> so there should be several results

Which exactly? On the screenshot only one (-175) does not comply the condition. <-200.

[Stefan]

yuri Thanks for the link, I will try that.

I'm not sure whether we're talking at cross purposes. But almost all values should fulfil the condition "less than -200".

-500 < -200; -400 < -200; etc

[yuri]

We would need absolutely all parameters of the report to be able to reproduce. Plus versions of Advanced Pack and Espo.

[Stefan]

yuri

I use custom entities for this report, so I don't know if it's that easy to reproduce it. But here is the whole report:



EspoCRM: 8.3.6
Advanced Pack: 3.4.5

I don't know SQL very well, just a little. I think that "HAVING SUM(cAusgaben.betrag) < -200" should not be inside the IN clause.

SELECT
DATE_FORMAT(cAusgaben.datum, '%Y-%m') AS MONTH:datum,
cAusgaben.c_empfnger_id AS cEmpfngerId, SUM((cAusgaben.betrag * betragCurrencyRate.rate)) AS SUM:betragConverted
FROM c_ausgaben AS cAusgaben
LEFT JOIN c_empfnger AS cEmpfnger ON cAusgaben.c_empfnger_id = cEmpfnger.id
LEFT JOIN currency AS betragCurrencyRate ON betragCurrencyRate.id = cAusgaben.betrag_currency
WHERE cAusgaben.id IN (
  SELECT DISTINCT cAusgaben.id AS id
  FROM c_ausgaben AS cAusgaben
  LEFT JOIN c_empfnger AS cEmpfnger ON cAusgaben.c_empfnger_id = cEmpfnger.id
  LEFT JOIN c_ausgaben_zuordnung AS cAusgabenZuordnungs ON cAusgaben.id = cAusgabenZuordnungs.c_ausgaben_id AND cAusgabenZuordnungs.deleted = 0
  WHERE DATE_FORMAT(cAusgaben.datum, '%Y-%m') IS NOT NULL AND cAusgabenZuordnungs.c_ausgaben_kategorien_id = '669bd58c659e66236' AND cAusgaben.deleted = 0
  HAVING SUM(cAusgaben.betrag) < -200
)
AND cAusgaben.deleted = 0
GROUP BY DATE_FORMAT(cAusgaben.datum, '%Y-%m'), cAusgaben.c_empfnger_id HAVING SUM(cAusgaben.betrag) < -200​

[yuri]

A HAVING filter along with a filter on has-many link does not work currently (works in some cases, but not in all). It's not possible to solve. It would require a massive rewrite in Espo itself to use sub-queries rather than joins for filters on foreign tables. Luckily today database systems perform faster with sub-queries than it used to. But no guarantees when/if we do it.

[Stefan]

Alright. Then I'll have to find another solution for this. Thanks yuri !