Reporting and Analytics for Service

Get Involved. Join the Conversation.


    Bastiaan van der Kooij
    Time in Status (Transactions Calculation w/ Custom...
    Topic posted April 28, 2016 by Bastiaan van der KooijGold Crown: 30,000+ Points, last edited April 28, 2016 
    1816 Views, 5 Comments
    Time in Status (Transactions Calculation w/ Custom Script) - Analytics Cookbook Recipe

    Time in Status

    Calculates time using custom script

    Author: Bastiaan van der Kooij

    Independent Oracle Service Cloud Consultant

    Difficulty rating:   (Advanced)

    Target persona: Anyone interested in Incident status transitions



    1 Custom Script

    Overall description/purpose: This report uses the status transactions from the transactions table to display all status changes from an incident, sorted by the date the status was changed. Using a custom script the time that the incident spent in that particular status is calculated and displayed back to the report user.

    Use case being served: This type of granular status data is not stored in the inc_performance table. Using the transactions table alone there is no way of calculating the time spent in a particular status. To avoid using external solutions this report with custom script can be used.

    Screen shot:

    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. 

      a.  There are 4 columns used in this report. The order of these columns is important as the custom script reads and writes these columns based on the order. (To make this more flexible check out the Answer Search in Source’ Recipe) The sorting order is important to ensure accurate calculation of each transition and should not be changed.

      b.  The Duration column just has a 0 for its definition, since this column will be populated by the custom script with the number of seconds that are between two status changes. Since we do want this to be displayed as time, we have to change the format of this column accordingly:

    2. Custom Script

      NOTE: Be sure to select “No” during the custom script prompt that occurs during the report import process. This custom script is necessary for this report to function properly.

      Since this is not a PHP lesson the script is functionally described:

      a. This script is a relatively simple script that runs for every row. It checks if the reference number is still the same as the previous row and if it is it will subtract the time of the previous row from the current row, therefore getting the difference in seconds. If it is a new reference number it clears the Duration columns ($rows[0][3] = null;) since the first status change is the created time and there is no duration. Finally it stores the current Reference number and current Time in a global variable, which will be used by the script for the next row, etc…

      b. The question & answer source of the answer are hidden columns as well so they can be picked up by the Custom Script.

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

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




    • Dietrik

      It looks like you're starting a restaurant. Three stars.

    • 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

    • Colin Campbell

      Hi Bastiaan,

      This is a beautiful and simple report, and something that I have been looking to find for a long time.  Thank you!




      Edited by eleep to remove question. Per my earlier comment on this thread, questions regarding this report recipe should be new posts in this forum board, not comments on this report recipe post.

    • Mateus Alves

      I'm testing the report, but when I add a "creation time" filter, the time-by-status information disappears.

      Do you have any idea why this happens?

    • Craig

      My feedback is that the "Duration" column is not truly representing the "Time in Status" but rather the time taken to get to that status from the previous one. In row 2 where Status = Waiting then ideally I'd like to see how long it was at "Waiting" for but instead the time is showing 2h39m59s - this is the time difference between the change from Research to Waiting and so is really the time spent in "Research" status.

      Is there any way to find $next_time and minus $curr_ref rather than looking at $prev_time for the calculation?