I am trying to set up a report that would break case counts down into 30 minute increments and group them. The smallest increment I have been able to break them down into is by day. Does anyone know of any ways that I am not finding to do this?
I am trying to set up a report that would break case counts down into 30 minute increments and group them. The smallest increment I have been able to break them down into is by day. Does anyone know of any ways that I am not finding to do this?
Nicole,
At this time Sugar can group by Day, Week, Month, Quarter, and Year. There is an existing Idea that was filed for the ability to also group by Hour and if implemented I feel it would get you very close to your request of 30 minute increments.
https://portal.sugarondemand.com/#supp_Bugs/54425
I cannot speak to when you can expect to see this in the product however you may use the link above for real-time updates to this request. When 'Fixed in Release' is populated with a Sugar version, you can expect to see this capability in that build.
Hi Nicole McGath,
This type of report is possible using Sugar's Advanced Reports feature. If you're not familiar with the feature, Advanced Reports allows you to build a report based on one or more SQL queries. You cannot generate any charts with Advanced Reports in Sugar, but the information is easily exportable if you want to build a chart in another application.
The following custom query will generate the 30-minute intervals and how many case were created in their respective interval for the month of June 2023:
SELECT FROM_UNIXTIME((UNIX_TIMESTAMP(cases.date_entered - INTERVAL 240 MINUTE) DIV (30* 60)) * (30* 60)) cases_30_min_date_entered, COUNT(cases.id) cases_count FROM cases WHERE (cases.date_entered >= '2023-06-01 04:00:00' AND cases.date_entered <= '2023-06-30 03:59:59' ) AND cases.deleted=0 GROUP BY UNIX_TIMESTAMP(cases.date_entered - INTERVAL 240 MINUTE) DIV (30* 60) ORDER BY cases_30_min_date_entered ASC;
A few notes about the query:
WHERE (cases.date_entered BETWEEN NOW() - INTERVAL 30 DAY AND NOW()) AND cases.deleted=0
I hope this helps!
Chris