Search

How can I customize the display of certain records in a report?

Environment:

Analytics, Data Exceptions
Oracle B2C Service, all versions

Resolution:

Data exceptions are used to highlight data that meet specific criteria within the overall report. This allows you to customize how certain records display in the report so that some records display in a different color or font style.

Note: Data exceptions are not used to restrict data included in the report based on certain criteria you specify. That is, run-time and fixed filters are used to determine which data is included in your report.  Exceptions are used to affect the display of certain data within your report. 

In the report, the data that matches the data exception is flagged in some fashion, such as with an asterisk or with a different color or font. For example, if your report includes all unresolved incidents assigned to a group, you can use a data exception to have all incidents that are assigned to a specific staff member display in a different font, such as bold or italics.

You can define multiple data exceptions to use within a single report. This allows you to highlight multiple kinds of records for your audience.

For more information on creating and editing custom reports, refer to Answer ID 2509: Creating a Basic Custom Report.

Conditional formatting:  You can also use conditional formatting to customize the display of cell values in a specific column of your report. For more information on conditional formatting, refer to Answer ID 2532: Conditional Formatting of a Column in a Report.

Adding Data Exceptions to a Report

Each data exception consists of an Expression, Operator, and Value. These define the criteria for the record to match. If a record matches the criteria, that record displays based on how the Tabular and Graphical displays are set (including color and font). The Tabular style applies to records listed as output. The Graphical style applies to data points in a chart or graph.

You must create separate data exceptions for each different color or style. For example, if you want some records to display as red, some as blue, and some as green, you must create three data exceptions -- one for each color and criteria.

To define data exceptions within a report, use the steps below:

1. Open the report for editing. 
    
2. Click the Display tab and enable the Exceptions checkbox.  Click the Insert tab on the ribbon, click in the Exceptions button to Add an Exception.
   
   Note: To edit an exception that already exists, right-click on the exception listed and select Edit Exception. 
    
3. In the pop-up window, add a name for the exception and set the display reference to be a specific output field or the entire row for the record. 
    
4. On the Definition tab, specify the criteria used for the exception. For example, to set incidents with an unresolved status to be red, set the contents on the Definition tab to be:
    
   Expression: incidents.status_type
   Operator: in list
   Value: Unresolved (enable checkbox) 
    
5. On the Tabular Display tab, specify the format and color used to display the record when listed in a table. You can use default display options or define a custom display option.  You can modify the color, data symbol and font style that will be used to display records that match the criteria on the Definition tab. 
    
6. If your report includes a graph, on the Graphical Display tab, specify the color associated with the exceptions. 
    
7. Click OK to close the exception window. 
    
8. Add or edit other exceptions as needed. 
    
9. When finished, save your report.

Run your report to verify that records meeting your exceptions display as you intend them to.

Examples of Data Exceptions

Basic data exceptions:  The most basic data exception involves a single field in the definition.  This allows you to define the criteria using an operator and value that relates directly to that field.

For example, if you want to define a data exception that displays all Updated incidents in blue, you can set the criteria to be:

Expression: incidents.status_id
Operator: in list
Value: Updated

Then, define the tabular display to use blue for the cell color or blue for the font color.

Exceptions based on multiple criteria:  You may want to create a data exception based on the values of multiple fields. To do this, you can use an IF statement in the Expression of the exception.  Then set the data exception similar to the following:

Expression: if(condition1 & condition2 & condition3,1,0)
Operator: equals
Value: 1

This means that if condition 1, 2, and 3 are all met, use a value of 1 for the expression, otherwise use 0. Then compare the expression value (1 or 0) to 1.  If the expression is 1, then the data exception is met.

Note: To join multiple conditions in the first part of the IF statement, use an ampersand (&) for AND and use a pipe ( | ) for OR.

For example, to create a data exception for incidents that are in an updated status and have been updated more than an hour ago, the data exception is:

Expression: if(incidents.status_id=8 & date_diff(sysdate(),incidents.updated)>3600,1,0)
Operator: equals
Value: 1

In this case, there are two conditions:

• Condition1 is: incidents.status_id=8
   
  where 8 corresponds to the numerical database value for the Updated status. You can get the ID by going to the incidents statuses table (from the Service Administration items > Customizable Menus) and hovering over the Updated status. 
   
• Condition2 is: date_diff(sysdate(),incidents.updated)>3600
   
  For this condition, 3600 corresponds to the number of seconds for the interval of interest (one hour = 3600 seconds).
   
  The value is determined by using a date difference between the system date/time and the value of the incidents.updated field.  If the difference between the system date and the updated value exceeds 3600 seconds, the condition is met.

Finally, if both conditions are met, than the entire expression has a value of 1 based on the IF clause.  Otherwise, the expression has a value of 0.  Only records that meet both conditions match the data exception.