Reporting and Analytics for Service

Get Involved. Join the Conversation.


    Jerremy Townsend
    Incident Response Monitoring - Analytics Cookbook Recipe
    Topic posted July 6, 2016 by Jerremy TownsendRed Ribbon: 250+ Points, last edited August 24, 2016 
    645 Views, 1 Comment
    Incident Response Monitoring - Analytics Cookbook Recipe

    Incident Response Monitoring

    Agent Last Response


    Author: Jerremy T

    Oracle Service Cloud

    Analytics Product Manager


    Difficulty rating: (Intermediate)

    Target persona: Managers, Executive


    2 Tables (Incidents, Transactions). 1 Inner Join

    1 Column using formulas (1 date_diff())

    4 Filters

    1 Rollup

    1 Slice

    3 Exceptions with formatting

    Overall description/purpose: This report allows managers and shift leaders to assess at a glance if agents are responding to incidents in a timely manner.  The use of exception based formatting in combination with slicing provides easy to read and process data.

    Additional slicing and filters can be applied to this report so that it fits into your organization's business processes.  Adding shifts or groups would allow a manager or shift leader to view only their direct reports.  Adding more times or time ranges can be easily done by adjusting the calculated field and changing exception formatting.

    Maintaining and monitoring incident repsonses is a great way to avoid customer frustration and provide world class support.

    Screen shot: All slices

    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. Columns & variables are shown in the PDF definition.  These are additional comments about those.

      a.   Last Reponse Slice - This is hidden column using a nested IF statement to create the slice values. The IF function can be nested, when you have multiple conditions to meet. The FALSE value is replaced by another If function to make a further test.  An example of this would be varieties of apples; If(apple.color = green, "granny smith", If (apple.color = red, "red delicious", "Just an apple"))

      b.  Last Response Age - This column is using the date_diff function to calculate the amount of time that has elapsed since the last response.  For more information on the date_diff function please refer to the Functions - date_dif() video tutorial.

    2. Tables Used in this report:  The incidents table supplies the last response (incidents.last_resp) data for this report. It is joined to the transactions table so that the report can show which account made the last response (transactions.acct_id) as opposed to who the incident is assigned to.


    1. Filters Used in this report: I used 4 filters in this report.  Two of the filters are on the transactions table.  The transactions table is typically one of the largest tables in the database.  Creating filters on this table can significantly improve the report performance and assist in avoiding any potential max join issues.

      a. transactions.created = incidents.last_resp - This filter uses the last response (incidents.last_resp) field value to narrow the scope of our query on the transactions table.  As an added benefit the transactions.created field is indexed in the transactions table which provides improved performance when querying the database.

      It is important to note that this filter is used to eliminate duplicate response type transactions. Allow me to explain. If an incident has had multiple responses each of those responses equates to a response type record in the transactions table. Here I use the incidents.last_resp value, datetime, to only show the transaction record with the same value or date and time.

      b. I've included a filter on incidents.queue_id but you may not find this necessary.  You may want to see all of your queues or you may not use queues.  This was also my roll up as you can see below.

    2. Formatting:

      a. Rollups: A rollup was added to the Queue (incidents.queue_id) column in order to reduce the amount of data that I was viewing.

      b. Slice: A slice was added to the Last Response Slice column in order to see the various exceptions.

      d. Exception based formatting: There are 3 exception formats added as follows:

      i. 0 - 24 hrs - I used an expression (date_diff(sysdate(),incidents.last_resp)) here to highlight the "normal" time of less than or equal to 24 hours.  You'll notice that in all of the exception values I used seconds to evaluate.  This is because the date_diff function returns a value in seconds by default.  There is no change to the format of rows meeting this exception criteria.  I simply wanted to include these in my tabular display for the percentage of incidents that meet this.  This may provide the ability to see how well we are meeting our SLAs at a "glance".

      ii. 24 - 48 hrs - Just like the first exception only I used a between operator and the number of seconds that represent 24 - 48 hours (86401 to 172800).  I also changed the background style for this to be yellow.  This may indicate to a manager or lead that some incidents are approaching my 48 hour response SLA and should be reviewed or "scanned".

      iii. Greater than 2 days - Here again I have used the the IF function to evaluate if the value is greater than 2 days or 172800 seconds. The exception formatting changes the background to red and font to bold in order to bring it to the attention of the viewer. In my business case this would indicate that were are not meeting our SLAs for those incidents.

      Technique Note: Please review the "When to Choose Exceptions vs. Conditional Formatting" technique video to understand the difference between these two formatting options.

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

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




    • Erica (Leep) Anderson

      We are 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 Jerremy'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