Reporting and Analytics for Service

Get Involved. Join the Conversation.


    Larry Chien
    Incident Arrival Patterns (30 Day Graphical Rolling Avg...
    Topic posted May 31, 2016 by Larry ChienGreen Ribbon: 100+ Points, last edited June 22, 2017 
    1147 Views, 5 Comments
    Incident Arrival Patterns (30 Day Graphical Rolling Avg by Hour) - Analytics Cookbook Recipe

    Incident Arrival Patterns

    Past 30 Day Avg Create Rate by Hour


    Author: Larry Chien

    Difficulty rating:  (Intermediate)

    Target persona: Call Center Manager, Workforce/Staffing Management Team


          Report Top Level

    3 Columns using formulas (1 count distinct() & 1 date_format())

    1 Computed Column (Rollup % of Total)


    Charts (Stacked Vertical Bar Chart)

    1 Calculations (Report Sum)

    1 Crosstab Report Output Option

    1 Drill down level

          Report 2nd Level (Channel Breakdown):

    Columns using formulas (1 nvl() & 1 count distinct())


    Overall description/purpose: This report shows the number of incidents created over the past 31 days (rolling), grouped together by the hour of the day to show the average largest/smallest submit times in your business day.

    Use case being served: The purpose of this report is to help identify channel-specific incident arrival patterns and trends to help with resource management and agent forecasting / scheduling.

    Screen shot: 

    Special functionality usage: Cross tab report presentation is used to group the data returned by both row and column to easily view channel-specific incident counts.

    High level flow for report creation: This is a two-level report where the top level gives you creation rates by hour, and then the drill down shows channel breakout by Incident Assignment Group.  This will allow the report reader to determine if there are channel-specific volume trends by assignment group, in the case that their Organization’s account groups are separated by any variety of characteristics (i.e. language, physical location, tier, loyalty / VIP, etc.)

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

    Top Level 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: 

      a. Computed % of Total – This field is simply added by adding the Count () column first, then selecting that column and choosing the ‘ƒx Insert Column > Report Percentage of Total’ selection from the Computed Area of the ribbon

      b. Variables used - $Hour - This is used to be able to format the incident created field by the numeric military hour of the day, which is not an available Column formatting option.  To add this variable, navigate to the Home Menu > Variables.

    2. Filters: I’ve included a custom list of filter items, which lists the commonly encountered incident sources.  If your organization has other incident sources that need to be tracked, or if there are channels that are not currently used, the filter can be adjusted as necessary.


    3. Charts:  This report uses charts that are...

      Stacked Bar Graph setup

    4. Formatting:

      a. Cross Tab:To create the cross tabs once the columns are pulled in, navigate to the Display menu and select Cross Tab > Choose Columns:

      b. Calculations:  Adding a Report Sum (∑) Calculation is done as simply as clicking on the column you wish to sum up to select that column as the active column, and navigating to the Calculations Area in the ribbon and clicking on the Sum icon:

    Drill Down Levels: Yes

    Second Level Report Configuration Details

    1. Special Function Consideration - Functions Used on the Group / Team Assignment field – I’ve used an nvl() function on the to show ‘Not Assigned' vs. No Value when no value is returned.   This can be adjusted as needed in the column definition.

    1. Rollup: Adding a column Rollup can be accomplished by clicking on the desired rollup column to select that column as the active column, and navigating to the Rollup Area in the ribbon and clicking on the Rollup icon:

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

    • Human readable PDF report definition
    • XML Report definition 



    • Erica (Leep) Anderson

      Just wanted to give a BIG thanks to you, Larry, for contributing this report recipe! 

      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 Larry'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

    • David Queen

      Hi Larry, this is fantastic- thank you. Being relatively new to OSC I'm always looking for anything that'll help my colleagues and me get the best out of the product. Not only do I see great use for your report, it's given me some valuable tips on how to create reports and use the functionality in the system. 

      I really am grateful to you for taking the time to share your report.

      Thanks again



    • Kristi A. Karels

      I love this report! I downloaded it and imported it and it works perfectly!   There seems to be missing incidents though because, when I run it - I don't see evidence that it includes incidents created by campaigns or surveys.  I don't see that these features are excluded in the filter - so I'm confused on how to incorporate the campaign/survey incidents created.   I have surveys for prospect intake that create incidents in them and also have campaigns that automatically review tasks due and create incidents as well. - so they are an important piece of our business model and I don't see a way to draw them into this report.

    • Danette Beal

      Hi Kristi,

      I ran this one past our Analytics PM and what you will want to do is click the customize link in the filter window. This will allow you to see what customization the recipe author put in. Check out the image below where the cursor. 


      0ZMLsw9.png" />"


    • Kristi A. Karels

      WOW!  I have never noticed this option before!!  It helps to see additional options - but I still don't see incidents created from campaigns.  What would I be looking for? 


      I have a 2 step campaign that has a entry point that I link to a data import.  This way I can import *new* contacts and create/update contact records AND create incidents for them. However, I cannot find the incidents that I have created using the campaigns with this report.