Reporting and Analytics for Service

Get Involved. Join the Conversation.


    Bastiaan van der Kooij
    Incident Alert Report - Comparing Today vs. Past Submit...
    Topic posted April 28, 2016 by Bastiaan van der KooijGold Crown: 30,000+ Points, last edited June 22, 2017 
    854 Views, 4 Comments
    Incident Alert Report - Comparing Today vs. Past Submit Rates - Analytics Cookbook Recipe

    Incident Alert Report

    Compare Today's Incidents

    Author: Bastiaan van der Kooij

    Independent Oracle Service Cloud Consultant

    Difficulty rating:   (Advanced)

    Target persona: Shift Leads & Managers


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

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

    1 Exceptions

    Overall description/purpose: This report is setup as an alert report when incidents created today breach a certain threshold. The data that the report shows is to inform the recipient of the mail which threshold has been breached.

    Use case being served:  Often managers and other employees responsible for the Service Department are not working in OSC fulltime and are not aware of sudden peaks in incidents that are coming in. This report can be used to define multiple thresholds (dynamic / static) and multiple recipients to allow management to quickly respond to peaks in incidents.

    Screen shot:

    Running the Report:  The basic functionality of this report is to trigger an exception when the number of incidents created today, are higher than the threshold. The threshold can either be a static number, or a comparison to the incidents created in a reference period using a percentage. The requirement for this report to run correctly is that at least one incident per day is created for the reference period.

    Threshold Type

    The user can select two options for the Threshold Type: Dynamic & Static. When Static is selected the value in the Static Threshold filter is used as the threshold. So when it is set to 250, an exception will be thrown when there are more than 250 incidents created today.

    When the threshold type is set to Dynamic the threshold is determined on the average number of incidents that are created in the reference period defined in the ‘Reference – From’ filter. It looks then at the percentage set in the Dynamic threshold to determine when the exception is thrown. So when the reference from period is set to -1 week it takes the average number of incidents last week till today. Suppose the average is 50 incidents and the Dynamic Threshold is set to 120%, then the exception will be thrown when there are 60 incidents or more created today.


    Setting the Reference

    The reference period that is used in the Dynamic Threshold Type can be manipulated further using the following filters;

    • Reference Behavior
    • Include Weekends?


    The reference behavior can be set to ‘Daily Total Average’ or ‘Daily Running average’. The former takes all incidents created in the reference period into account, the latter only looks at incidents created until the same time it is when the report is run. So when the report is run at 10:00 and the reference period is -1 week and the behavior is set at Running Average, it looks at all incidents created up to 10:00 from last week until today.

    Finally the ‘Include Weekends’ filter can be used to exclude weekends from the reference behavior so  it will not lower the average incidents in the reference period unnecessary.

    Setting up the Schedules

    Since Analytics allows for multiple schedules to be created for each report, it is possible to use this one report to define many different alerts, based on different thresholds to different people on different times. When you create a schedule you simply define the right Filter values and the recipients. You must select the Threshold Exceeded Exception in the Alerts box to ensure that the report is only sent when the exception is actually triggered. 


    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 Overview

      Without variables it would be impossible to build this report, since they provide the mechanism of passing through user input to a filter or to a column in a user friendly way. Below are some examples of the variables and how they are used;

      a. Threshold Type: This is a Integer User Defined List. The user selects a value that makes sense like “Dynamic” which is passed to the filters and columns as 1, so you can keep the logic simple and just say something like if($threshold_type=1,'"YES!")

      b. Static Threshold: This is a simple integer Ad Hoc variable to pass an integer value to the columns & exception so it can be used to calculate.

      c. Include Weekends: a Yes/No Predefined list to be used in columns. Again a user friendly way to select a value which is translated into 0 / 1

      d. Average per Day: This variable is not used for the user to fill in but is there to keep the report clean. And make it easy to modify the average value without diving into highly complex column values. When you look at the Threshold Exception you can clearly see how the variables makes it much less cluttered and more easy to see what this expression is doing.

    2. Tables, columns, and filters definitions are shown in the PDF definition. Although this PDF view will provide much of the needed information, I would like to specifically point out the use of some functions in the variables / columns:

      a. $average_per_day variable:

      count(incidents.i_id) / count(distinct date_format(incidents.created,'YYYYMMDD'))

      This function counts all incidents and divides it by the unique number of incident.created values it finds, thereby generating an average value per day.

      b. Include Weekends Filter:

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

      c. Threshold Type Column:

      if($threshold_type=1,if($reference_behaviour=1,concat('Dynamic - Daily Total Average (',$threshold_percentage,'%)'),concat('Dynamic - Daily Running Average (',$threshold_percentage,'%)')),concat('Static (',$static_threshold,')'))

      This column is just used to show the receiver of the mail which exception was thrown and what the actual values are. It uses a nested if statement and the many variables that are available to build this string. The logic basically breaks down into the following parts (whereby the output is in “”);

      ThresholdType = Dynamic

      Reference Behaviour = Daily Total Average -> “Dynamic – Daily Total Average 120%”

      Reference Behaviour = Daily Total Average -> “Dynamic – Daily Running Average 120%”

      ThresholdType = Static > “Static (120)”

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

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




    • 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

    • Makarand Malandkar

      A masterpiece recipe. Not sure if anyone else experienced it but I noticed a strange behavior for output column "% of average" and it wasn't showing correct value. This could be due to difference of data type of average incidents (decimal)  and # Incidents Today (number). After truncating the average incidents column the % was calculated as expected.

      Thanks Bastiaan for your great recipes/posts, they save the day for us.



    • Danette Beal

      Thank you for the feedback Makarand and letting us know how helpful this report was.

      Bastiaan is one of the great is our Community and its good to hear he continues to positively impact the Community!


      Danette, Community Manger

    • Bastiaan van der Kooij

      hey Makarand,

      I have checked the report and also encountered the problem. This was not there when I created the report (otherwise the screenshot would have been impossible), so it must be something that changed during a version change, but I cannot replicate that anymore since I do not have any old sites lying around.

      However I have identified the issue and fixed it. It does not have anything to do with the difference of data type since dividing an integer by a float should be possible. The problem was with the usage of the variables in the column and the exception. Basically the two variables where 'syntactically' like;

      $this_day_count : count(A)
      $average_per_day : count(B) / count(C)

      Those two were combined in the column as follows;
      ($this_day_count / $average_per_day) * 100

      This actually translates into;
      (count(A) / count(B) / count(C)) * 100

      instead of;
      (count(A) / (count(B) / count(C))) * 100

      So you can see why truncating helped here since it escaped the variable as well, however with truncate you loose the precision which is not needed. I have updated the report definition and placed the $average_per_day variable between brackets, so both in the column and the expression it works ok now.