Grouping by Month in Reports

[bandtank]

EspoCRM allows report results to be grouped using various date functions. Grouping by month is possible, but a custom formula must be used. Grouping by month usually means:

Month Count
01    8
02    7
...

One of the options in the GROUP field is MONTH: (...). The results may not be what one might expect:

Month   Count
2024-01 4
2024-02 3
...
2025-01 4
2025-02 4
...

EspoCRM groups records using the following SQL when MONTH is selected:

GROUP BY
  DATE_FORMAT(performanceReport.date_start, '%Y-%m');

instead of

GROUP BY
  DATE_FORMAT(performanceReport.date_start, '%m');​

The good news is support provided a solution that works well. In the GROUP field in a report, use the following code to group by month:

MAP:(MONTH_NUMBER:(dateStart),
  1, '01',
  2, '02',
  3, '03',
  4, '04',
  5, '05',
  6, '06',
  7, '07',
  8, '08',
  9, '09',
  10, '10',
  11, '11',
  12, '12'
)​

Like this:



Here is a screenshot of the rest of the parameters:


Here are the results:

[yuri]

Thanks for sharing. Worth noting that this is achievable for non-developer users too.

[bandtank]

Good point. Is there a tutorial forum for non-developer users? I didn't see one, but maybe I missed it. If so, i will post it there as well.

[yuri]

There's no such.