Conditional report formatting enhances the readability of a report, making it possible for your users to quickly spot values of interest, patterns and trends. It allows you to specify formatting rules based on the values and is only available in matrix reports. You can use conditional report formatting in conjunction with display and numeric report formatting.
Conditional formatting can be applied to any row, column or spreadsheet tab element on a matrix report that is not a Design Element. Different values in a single row or column may have separate formatting applied to them allowing important values to stand out. In addition, multiple rules can be assigned to a single element. Conditional formatting cannot be applied to elements in the filter, nor can it be applied to segments or parameters. You can also specify conditional formatting for an entire Matrix report. To do so select Report Properties from the toolbar and then select the Conditional Formatting tab in the dialog box.
Conditional Formatting Format Options
The format options available for Conditional Formatting are the same options available in the Display Format dialog and include the option to show the values that meet the conditions in the following ways:
- As is: when you first create a format, "As is" is the default setting. When applying multiple rules, the "as is" option has the lowest precedence. Any other formats override this option. If there is not a format that overrides this option, then it defaults to show as number.
- Number: this option shows the numeric value.
- Blank: this option will not show any value for numbers that meet the condition. This setting is useful when actual values are not important. With this option, background color may be used to indicate cell value or importance.
- Icon: with this option, icons are displayed instead the number where the condition is met.
Apply Conditional Formatting to a Report
- If you are adding conditional formatting to a report element, right-click the report element and select Properties from the right-click menu. If you are adding conditional formatting to the entire report, select Report properties from the toolbar.
- In the Report Properties dialog box, select the Conditional Formatting tab.
- Select +New Rule. The Conditional formatting rule editor appears. Use this dialog to create new rules or edit existing rules. This editor has two sections: Apply when value is and Format. The Apply when value is section is where you specify the conditions under which the formatting is applied and the Format section is where you specify the how the data that meets the conditions is formatted.
- Select the operator for the condition you are creating from the drop-down list. The following operators are supported:
- less than
- less than and equal to
- greater than
- greater than and equal to
- equal to
- not equal to
- not between
- Enter the values for the condition you are creating. Your entries must be numeric. All operators except between and not between require a single input. Between and not between require two inputs.
When applying rules on percentages, use the appropriate values. For example, use 1 for 100% or 0.3 for 30%. To create a rule that displays a red font for values between 10% and 30%, use: "value between 0.1 and 0.3" and then select a red font color from the Format section.
- If you want the condition to apply to the rollups only, select the Apply to rollups only check box. If this check box is not selected, rules are created as data condition rules under the Data Rules section.
- In the Format section, specify how you want the data that meets the condition to be formatted.
- Select Add.
The rule is added to the Data rules list.
- Select Apply and save the report.
Note: You can repeat these steps to add additional rules to the element. The rules are applied in the order shown in the Data rules editor and there is no limit to the number of rules that you can add.