Reporting and Analytics for Service

Get Involved. Join the Conversation.


    Bastiaan van der Kooij
    Hourly Incident Activity Today - Analytics Cookbook Recipe
    Topic posted April 28, 2016 by Bastiaan van der KooijGold Crown: 30,000+ Points, last edited June 22, 2017 
    1354 Views, 3 Comments
    Hourly Incident Activity Today - Analytics Cookbook Recipe

    Incident Activity

    Hourly Incidents Activity Today

    Author: Bastiaan van der Kooij

    Independent Oracle Service Cloud Consultant

    Difficulty rating:   (Advanced)

    Target persona: Shift Leads & Managers


    Columns using formulas (date_group,sum, if statements, round)

    Variables (Integer User defined List, Replacement Value, Yes/No predefined list)

    1 Line/bar combo chart

    1 Calculations (total)

    Overall description/purpose: An operational report that shows the incident created / updated today per hour. It compares it to the average of previous days in the reference period. This allows managers to identify in real time any spikes in incidents and gives the possibility to predict the rest of the day all in one view.

    Users can easily change the behavior of the report (see screenshot below)

    1.Show the count of Incidents created, updated by customers or both

    2.Modify the date range of the reference period: 1,2,4 or 8 weeks back

    3.Whether or not to include weekends in the reference

    Screen shot:


    Special functionality usage: Since the Incident table is added into the Table join you can easily add your own filters to look at specific incidents only. In this report there is already the queue and category filter, add any others that you might need.

    In principle this report can be used to count any transactions or other data and provide a comparison to the reference period. 

    Master Chef Approved (a.k.a. Data Validated)? Yes

    Report Configuration Details

    The following section is intended to detail specific areas of the report that are not well described by the PDF report definition.

    1. Variables: 

      a. Reference Period: this is an Integer User defined list. This variable is needed to ensure that the reference period is always exactly one or multiple weeks back rounded. The integer value of 1 Week simply translated into 1 which is used in the filters to define the actual date.

      b. Today Start: A replacement value that holds the date value of today 00:00. This is just a convenience variable to be used in filters and columns

      c. Same Day of Week: When the transaction date is the same weekday as today it turns 1. This is used in the Average Day of Week column

    2. Filters:

      a. The report can load created transactions and updated whereby the status id = 8. To make it easier for users to select the transactions they want, the variable Type is used. The filters look at this type to get the right transactions likes so: type!=Created & transaction_type = updated & transaction.id1=8 (updated) OR type != Updated & transaction_type = Created.

      b. date_trunc(date_add(sysdate(),-$reference_period*7,DAYS,0),DAYS)

      This filter gets transactions in only when they are older than the number of weeks defined in the Reference Period variable. The date_trunc is used to round it to the start of the day and convert it into a proper date, since without it the report throws errors.

      c. if($include_weekends=1 ,1,to_number(date_format(incidents.created,'%w')))

      The filter checks if the variable $include_weekends is set to 1, if it is it outputs always 1. If it is not it outputs the weekday number of incidents.created. The output should be between 1 – 5, therefore always allowing the 1 when $include_weekends = 1, otherwise Monday to Friday (1-5).

      d. transactions.created < now

      This filter might seem strange but it is only used to close of the Reference period, so that date_group can be used. See Hour column below.

    3. Columns:

      a. Hour: date_group(transactions.created,'%H:00')

      This column uses the date_group function to show each hour of the day. Since there might be hours in the midnight where not a single incident is created date_group is used instead of date_format to ensure all hours are listed even when they have no transactions.

      b. Average: round(sum(if(transactions.created<$today_start,1))/ if($include_weekends=1,$reference_period*7,$reference_period*5),0)

      It gets incidents older than today and divides them by the number of days in the reference period. When there are no weekend days included the number of days per week is reduced to 5.

    Included are the following (in a .ZIP file):

    • Human readable PDF report definition
    • XML Report definition 
    • Screen shot 



    • Dietrik

      Nice recipe Bastiaan!

    • Erica (Leep) Anderson

      Just wanted to give a BIG thanks to you, Bastiaan, for contributing multiple great recipes to the Analytics Cookbook launch! We are excited to have a reporting guru like you involved and supporting this new collective community resource!


      We are experimenting with leaving these report recipe posts open for comments, because we want report chefs to hear how their report recipes helped you. If you found this report recipe helpful, please give Bastiaan's post a thumbs up and leave a comment letting him know you appreciate his help, how this report helped you and your organization, etc.

      If you have a question about this report recipe, please start a new topic in the "Reporting and Analysis" forum board and link to this report recipe in your question. DO NOT post your reporting question here. We will remove any comments asking for help in the comments of this post.

      Erica, Community Manager

    • Kristi A. Karels

      Agree!  I like how this looks!heart