I need to create a report that shows the Case high traffic trends for each hour of the day for the current year. Basically, the customer wants to see a report for the current year of cases opened, grouped by the hour in which it was opened. So then they can see there busiest times of day, on average for the year.
For instance, if the case was opened at 11:30 am, then it would fall in the 11:00 -12:00 group.
I have a few ideas of how to accomplish this, I just want to make sure I am going about it the correct way.
Create a report and/or dashboard purely in Apex and VF. Seems fairly straight forward to create, just want to make sure I need to go fully custom before I decide to go that route.
Use a formula field that looks at the Date/Time opened field and pulls just the time portion and using a CASE statement populates the appropriate time interval to the field. Use standard reporting on that field.
Use some scheduled/batch apex or a trigger to populate the time interval field using the Date/Time Opened field, and then use standard reporting on that field.
I am fairly confident that any one of these ways would work, I just want to make sure I'm doing it in the most efficient way and I am not reinventing the wheel. So any thoughts and or tips from people that have implemented something similar would be much appreciated.