Reporting and Analytics for Service

Get Involved. Join the Conversation.


    Bastiaan van der Kooij
    Incident Transaction Counts (Multiple Transaction...
    Topic posted April 28, 2016 by Bastiaan van der KooijGold Crown: 30,000+ Points 
    2211 Views, 8 Comments
    Incident Transaction Counts (Multiple Transaction Types) - Analytics Cookbook Recipe

    Incident Transactions Counts

    Vanilla Report Counting Multiple Transactions Types

    Author: Bastiaan van der Kooij

    Independent Oracle Service Cloud Consultant

    Difficulty rating:   (Intermediate)

    Target persona: Shift Leaders & Managers


    10 Columns using formulas (min, max, sum & if statements)

      1 Rollup

    11 Calculations

    Overall description/purpose: This report can be considered as a Shift Leader / Management report for a day-to-day operational overview of the performance of agents. It is an intentionally plain report to show the possibilities of the transactions table when reporting on actions taken on incidents. 

    Use case being served: A vanilla report that can be used as a starting point in defining your own report based on the transactions table and defining your own KPI’s by combining the different columns.  Conditional or exception based formatting could easily be added to highlight specific thresholds for your business processes.

    Screen shot:

    Special functionality usage:  By using the transactions table you are always sure that you have up to date data since transactions are updated real time. You might consider defining your own KPI’s based on two (or more) columns in this report by simply combining them in a new column. For example Effectiveness = #edits / #solved.  To accomplish this you can copy the column definitions from both “# Edits” and “# Solved”, combining them into a new column.  Similar to the following:

    sum(if(transactions.trans_type= 3, 1)) / sum(if(transactions.trans_type= 6 & transactions.id1 = 2, 1))

    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.

    Tables, columns, and filters definitions are shown in the PDF definition. 

    a. This report does not use the incident table to increase performance, by simply adding a filter on the transactions table;

    transactions.tbl = Incidents

    b. The basic setup of the columns is always in this format;

    sum(if(transactions.trans_type= 2, 1))

    Visit the data dictionary and look at the transactions table to lookup what is stored in each column to define your definition:

    c.  The column definitions in this report are setup to count every occurrence of a transaction (e.g. including multiple solves per incident).  It is possible to change the column definitions to count unique incidents a transaction happens against (e.g. unique # of incidents solved rather than of number of times an agent set a status to solved).  To accomplish this you can slightly modify the column definitions to use “count(distinct” along with the, which is the same as the Incident ID, as follows (using # Solved as an example):

    count(distinct if(transactions.trans_type= 6 & transactions.id1 = 2,

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

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



    • Erica (Leep) Anderson

      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

    • Sasi Gudimetta

      Hi Bastiaan,

      This is an amazing report and it helped me solve a query in one of my business scenarios that went unnoticed for a while.

      I created a report that counted the number of responses that were being sent out by advisors using the threads table (threads.entry_type and threads.chan_id). After I built your report i noticed that there were some count mismatches between the numbers in your report's #Response Sent and the one that I built.

      I created drilldowns in both reports and checked the counts. Turns out that in the report I built, the count was coming a little higher because my report pulled in the incidents where a response was written and committed to the thread but the agent forgot to select 'Send on Save'. I alerted the business about this and the catch was well appreciated.

      So again. Many thanks for this beautiful report. Hope to see a lot more. :)

      A hugely appreciated Thumbs Up from my side. :)



    • Brian Priestley

      Thanks for putting all this together, we are new to RIght Now and I have found your report definition and explanation very clear in helping me understand where to look for the data and how to put a report together. It has been a great help and your effort is very much appreciated..

    • Donnie Guthrie

      Awesome report! I created this on our side and my boss loved it. 


    • Maheep Kaur

      The column 'updated' counts the number of Status Changed Transactions with id 8. When an incident is updated 2 times by the customer back to back, the transaction table has only 1 entry.The the total number of updates by the customer should be taken from from the Threads table.

    • Bastiaan van der Kooij

      Updated as in changed status to Updated. But fair point, shows the importance of definition. When you want the number of edits from the customer which I presume your definition is of updated then instead of using the threads table and making things complicated you can just use the edit transaction as well to show this;

      # Customer Edits / Updates
      sum(if(transactions.trans_type= 3 & transactions.source_lvl2 IN (5001,3002) , 1))

      # Agent Edits
      sum(if(transactions.trans_type= 3 & transactions.source_lvl1 = 32002, 1))

      source_lvl2 5001 = Techmail
      source_lvl2 3002 = EUP Incident Update
      source_lvl1 32002 = Console

      You can just play around with the Source ID's to specify exactly what you want to count. That is the whole purpose of this report to get you started with that.


    • Sasi Gudimetta

      Hi Bastiaan,

      One thing that I noticed in this report. In the #Assigned column the count being displayed are the accounts that have performed an assignment action as opposed to actual assignment of the incident to the designated staff account shown in the first column. (Since the table join between Accounts and Transactions is accounts.acct_id = transactions.acct_id)

      So basically the agent in question could have assigned the incident to him/herself or to anyone else and still the count would show up against their name. Shouldn't there be a way to segregate the #Assigned column in such a way that only the incidents that are assigned to that staff account be displayed there.

      Just my two cents. :)


      Sasi K Rao.

    • Bastiaan van der Kooij

      Hey Sasi,

      good point!

      If you would like to show any incident that was assigned to this agent you would join the accounts like we discussed in that other thread. You would need then to add a second join to the accounts table and end up with 2 transactions tables, each providing a separate flow of transaction types.

      It might be be better to rename the column to Assigned to Themselves (as in Fill Inbox), and change the definition of the column to only count transactions whereby the account is the same as the assigned account;

      sum(if(transactions.trans_type= 4 & transactions.acct_id = transactions.id2, 1))