Search

How do I include part of the incident thread in a custom report?

Environment:

Incidents, Analytics

Resolution:

Incident thread data is retained in a separate table from the general incidents table. In addition, there are several different types of threads, which allows you to configure your report to include only certain thread types, such as Customer Entry or Customer Proxy.

Important! Typically, reports that include incident threads become large very quickly and generally take longer to generate. When you create a report that includes incident threads, you should include a run-time filter that includes a range for the incident created date. That way, you can test your report for a small range of dates, which will allow your report to generate more quickly.

When creating a report that includes an incident thread, it is important to define the data set to use the Incidents table as the primary table and then join the Threads table to the Incidents table. To do this, use the steps below:

1. From the navigation area, select Analytics > Reports Explorer 
2. Select the New Report button from the Home ribbon
3. Select the type of report to add
   (See Creating a basic custom report for more information on creating a custom report.)
   
   
4. From the Home ribbon, click the Tables button
5. In the Data Set window, click Select Table
6. Expand the All Tables list and double click on Incidents (incidents). The incident table displays in the left frame.
   
   
7. Right click on the Incidents table and select Join To > Threads > incidents.i_id => threads.i_id. The threads table displays under the incidents table.
8. Click OK. The Data Set window closes.
   
   

With the data set defined properly, add filters and output columns to your report. 

To restrict the thread to a specific type:  Include a fixed or run-time filter based on the threads.entry_type field. 

1. From the Home ribbon, click the Filters button
2. Example filter definition would be:
   
   Expression: threads.entry_type
   Operator: equals
   Value: Customer (select desired thread type from drop down list)
        NOTE: Value selection options change depending on the operator selected.
        i.e. 'In List' allows you to select more than one thread entry type.
   
   
3. Click OK.

With the filter in place, you can add the thread as an output column for the report.  From the Threads table, drag the Text (notes) field to be an output column.  All thread of a type matching those set within your filter selection will be listed in the report results.

Reporting on the first thread entry

To include the first thread entry for an incident, first add a filter on incidents.ref_no or incidents.i_id; then use the following column definition:

substr(min(concat(date_format(threads.entered, 'YYYYMMDDHH24MISS'), concat(threads.seq, to_char(threads.note)))),16)

This can be modified to fit your needs.  For example, if you want to report on the most recent thread entry, change the min() function to a max() function.  Otherwise, if you want to return something other than the thread text, change the threads.note reference to your desired threads field.

For example:
-- Most recent thread entry per incident:
substr(max(concat(date_format(threads.entered, 'YYYYMMDDHH24MISS'), concat(threads.seq, to_char(threads.note)))),16)

-- The channel ID for the first thread entry:
substr(min(concat(date_format(threads.entered, 'YYYYMMDDHH24MISS'), concat(threads.seq, to_char(threads.chan_id)))),16)