Announcement

Collapse
No announcement yet.

Grid report: revenue per customer per month

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Grid report: revenue per customer per month

    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?​

  • #2
    I tried such a report and it worked for me.

    Click image for larger version

Name:	image.png
Views:	89
Size:	12.9 KB
ID:	109438

    Comment


    • #3
      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.

      Click image for larger version  Name:	sc01.png Views:	0 Size:	34.0 KB ID:	109444

      If I add this having filter to the report:

      Click image for larger version  Name:	sc02.png Views:	0 Size:	8.1 KB ID:	109445

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


      Here are also the parameters I use:

      Click image for larger version

Name:	image.png
Views:	71
Size:	13.9 KB
ID:	109446
      Last edited by Stefan; 08-18-2024, 10:25 AM.

      Comment


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

        Comment


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

          Comment


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

            Here's the SQL. Looks fine.
            Code:
            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.

            Comment


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

              Comment


              • #8
                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.

                Comment


                • #9


                  > so there should be several results

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

                  Comment


                  • #10
                    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

                    Comment


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

                      Comment


                      • #12
                        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:

                        Click image for larger version

Name:	image.png
Views:	84
Size:	32.3 KB
ID:	109484

                        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.​

                        Code:
                        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​

                        Comment


                        • #13
                          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.

                          Comment


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

                            Comment

                            Working...
                            X