Reporting and Analytics for Service

Get Involved. Join the Conversation.


    Kenny Tietz
    Team Open Incidents Summary (Team Dashboard) -...
    Topic posted April 28, 2016 by Kenny TietzBronze Trophy: 5,000+ Points, last edited October 14, 2016 
    1173 Views, 1 Comment
    Team Open Incidents Summary (Team Dashboard) - Analytics Cookbook Recipe

    Team Open Incidents Summary 

    (Team Dashboard)

    With Incident Details Report Link

    Author: Kenny T

    Oracle Service Cloud

    Analytics Product Manager

    Difficulty rating:   (Intermediate)

    Target persona: Agents & Managers


    9 Columns using formulas (7 count distinct (), 1 min(), 1 max() & date_diff())

    5 Exceptions with formatting

    3 Conditional formats

    1 Rollup

    1 Slice

    1 Menu item custom field

    2 Calculations (total and average on both rollup and report)

    1 Report Link

    1 Incident Details Report (Note: This is the drill down for this report)


    Overall description/purpose: Every customer has the need for a useful team dashboard that will tell them key aspects of their open backlog. This may include counts by agent, formatting/highlighting for certain metrics like age, past due initial response or resolution. This report recipe is intended to provide a roadmap for this common report.

    Use case being served: Oracle Service Cloud does have useful canned reporting on incidents and backlog. However, an agent dashboard is a business specific report that each customer must think about in detail. Ideally, the agent dashboard should help your agents by removing grey area from their decisions around what to work on next?

    Any report creation should start with lots of questions-"What is the story I am trying to tell with this report? What metrics are important to show to my agents? What amount of detail do my agents need without inundating them with too much information? How can you make important information stand out on the page for agents and managers to notice and take action on? What colors are right for my team? What subtle tools can you include for your managers to make quick and effective use of this report for their duties? Is my report intuitive? What level of training might be needed?"

    Screen shot: Top Level Report (1/2)

    Drill Down Report Link (2/2) - Incident Details/Agent Queue Report

    High level flow for report creation (optional):This agent dashboard will include aggregated information on the top level view with information rolled up by group and then agent. These aggregated values will take into consideration commonly used metrics with specific highlights. Exceptions are a useful way to add highlighting, because they give your users a legend at the bottom of the report to help decipher what the colors mean. A report link can be used to drill into the incident details (provided in a separate recipe).

    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. Tables, columns, and filters definitions are shown in the PDF definition. The PDF view will provide much of the needed information, but it is important to point out that this report is using a report link to an Incident Details report. In order for this report link to function properly, the following filters are needed in both reports:

      - incidents.status_id IN LIST

      - incidents.status_type IN LIST

      Functions: The following descriptions should help to understand the functions used in the column definitions:

      a. There are 5 count/aggregated columns in the report that use a formula similar to the following to aggregate based on unique incident ID:

      count(distinct incidents.i_id)

      b. The Largest Update Gap column uses max and date diff functions divided by 60 to give us the largest number of minutes between now and the last update for each agent queue. This column is then formatted to display these minutes in Day, Hour, Min format.


      c. The Oldest Incident column uses a min function to show the oldest reference # in each agent's queue.


    2. Special steps to create the report: The report utilizes a staff account custom field for Shift. The reason for this custom field is describe in the "slice" formatting below. This is a menu custom field with 3 menu items ("Shift 1 - AMER", "Shift 2 - APAC", "Shift 3 - EMEA") to address each of the 3 regions for a worldwide support organization.

    3. Formatting:

      a. Rollups: A rollup was added to the Group column (incidents.assgn_group_id), in order for staff accounts and their incident aggregations to be rolled up and collapsible by staff group.

      b. Slice: A slice was added as "links" on the Shift staff accounts custom field, which shows up at the top of the report. This will allow managers to click on specific regions to see how numbers look for agents associated to those regions. Additionally, there are denotations of "** UNASSIGNED **" and "** SHIFT NOT SET **" included in the formula used for the slice links. These allow a manager to quickly see the incidents in the list that are unassigned and/or staff accounts that do not have a shift set in their account record. This is an embedded tool to help indicate staff accounts that need this custom field set in order to take appropriate actions.

      c. Calculations: Rollup and report totals have been added on all # columns. A report average is also added to the Largest Update Gap column.

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

      i. # Assigned - Shows blue in cells if an agent has >10 incidents assigned to them.

      ii. # Unresponded - Shows purple in cells where agents have incidents without an initial response.

      iii. # Updated by Customer - Shows yellow for cells where agents have incidents with a status of "Updated by Customer".

      iv. # Sev 1 - Shows red in cells where an agent has one or more open severity 1 issue(s) assigned.

      v. # Sev Not Set - Shows plum in cells if there are incidents without severity set. This is a tool to help managers/site administrators see a process hole where severity is not properly being set. The admin/manager can drill into these incidents as examples to find the process gap.

      e. Conditional formatting: There is conditional formatting on the Largest Update Gap column which represents incidents in each agent's queue with the oldest updated time. This formatting shows as green, pale orange, dark orange for updates <2 days, between 2-5 days, and >5 days, respectively. This formatting also includes cell icons for the latter two colors.

    4. Options (Home Tab > Options > More Options):

      a. Reporting vs. Operational DB: This report is an agent dashboard report where real-time data is always desired making it necessary to set operational database in the report options.

      b. Uncheck the "Allow server to change data source as necessary option" to avoid automatic switching from operational to reporting DB if report approaches query size limit (2 million rows analyzed). Although unlikely, if this report queries too much data, it will fail with a "query processes too much data" error requiring further report optimization.  

      c. Set the "Open report without prompting for search parameters" option (also known as "initial run") to prevent the search dialog with every run because the report is already setup with the desired filters. This will save agents time.

      d. To refresh after data changes, set the "Refresh report when data shown is changed in the editor" option.

    5. Drill Down Levels: Yes --> Incident Details/Agent Queue Report

      a.  A drill down using report linking on the agent column was added to show individual incident details.

      b. The incident details report is in a separate recipe. However, I selected the following options on the filter page of the report linking wizard:

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

    • Report Definition -- Team Open Incidents Report (Top Level).pdf

    • XML report definition for direct import into a site -- Team Open Incidents Report (Top Level) 100724.xml

    • Full size screen shots of report -- Screen Shot - Team Open Incidents Report (Top Level).jpg



    • Erica (Leep) Anderson

      Just wanted to give a BIG thanks to you, Kenny, for bringing this idea to life and contributing this great report recipe of your own! 

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