Case Report grouped by Hour of Day

Question

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.

Possibilities include

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

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

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

Answer 1

I was able to accomplish this using just a couple of formula fields. I likely could have done it with just 1 formula field, but for readability sake, I split it into 2 fields. They are both formula fields of type text.

'Case Opened Time' Field Criteria

TEXT(
     CASE(
          VALUE(LEFT(RIGHT(text(CreatedDate),FIND(" ", TEXT( CreatedDate ))-2),2)), 
          00,19, 
          01,20, 
          02,21, 
          03,22, 
          04,23, 
          05,00, 
          06,01, 
          07,02, 
          08,03, 
          09,04, 
          10,05, 
          11,06, 
          12,07, 
          13,08, 
          14,09, 
          15,10, 
          16,11, 
          17,12, 
          18,13, 
          19,14, 
          20,15, 
          21,16, 
          22,17, 
          23,18, 
          00
      )
 )

NOTE: This is set up to convert the time to EST. If you do not want to convert to EST and leave it in GMT, then you would only need the formula to read

value(right(left(text(CreatedDate),find(":",Text(CreatedDate))-1),2))

This returns just the number of the hour. For example 1:35 pm -> 13, 8:45 pm > 20, 3:15 am -> 3.

So then I created another formula field to make this more readable for the reports. It really just put the time into military format and created a range for that time.

'Case Opened Time (Formatted)' field Criteria

IF( 
     LEN( Case_Opened_Time__c ) = 1, 
          '0' + Case_Opened_Time__c + ':00 - 0' + Case_Opened_Time__c + ':59',
          Case_Opened_Time__c + ':00 - ' + Case_Opened_Time__c + ':59'
  )

All this does is return the hour range that the case was created in. For example 1:35 pm -> 13:00 - 13:59, 8:45 pm > 20:00 - 20:59, 3:15 am -> 03:00 - 03:59

Now that I had the formula field that gave me this time range, I was able to simply set up a standard summary report that showed the number of cases for the year, by the hour of day, that the case was opened. Here is a shot of the chart for the dashboard

Hopefully this helps someone out that wants to do something similar to this.

Answer 2

I vote for option #2: using a formula field to extract the hour. I prefer this method because:

1. It is completely declarative
2. It is the simplest method