Skip to main content

logoCorrectSize.png

Adaptive Insights LLC.

Conditional Formatting for Reports

Explains how to apply conditional formatting to a report

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

  1. 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.
  2. In the Report Properties dialog box, select the Conditional Formatting tab.
  3. 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.
  4. 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
    • between
    • not between
  5. 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.  
  6. To apply conditional formatting to the element rollup values only, select the Apply to element rollup only check box. The conditional formatting shows for the element rollups only as opposed to the intersections of rows and columns. See Apply Conditional Formatting to Element Rollups.
  7. In the Format section, specify how you want the data that meets the condition to be formatted. 
  8. Select Add. 
    The rule is added to the Data rules list.
  9. 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.

Apply Conditional Formatting to Element Rollups

You can apply conditional formatting to only the rollup values for an element. When you run the report, the formatting shows only for the rollups and not for the intersections of rows and columns.

For example, a matrix report shows FY2014 as a column and you select to display Q1-FY14  through Q4-FY14 on the hierarchy. The report shows the following accounts on the rows:

  • PL Expense 
  • Non-Operating Expenses

You want to display the following conditional formatting in this report:

  • A red down-arrow icon for values greater than 500,000 for the FY2014 column element.
  • A green up-arrow for values less than 100,000 for the PL Expense row element.

Apply conditional formatting to rollup element

Do the following:

  1. In the columns, right-click FY2014 and select Properties.
  2. Select Conditional Formatting and add the following rule:
    when value is greater than 1000000, show iconred_down_arrow.png
  3. Select the Apply to element rollup only checkbox and save. Next, select Apply.
  4. In the rows, right-click PL Expense and select Properties.
  5. Select Conditional Formatting and add the following rule:
    when value is less than 3000000 show icon green_up_arrow.png
  6. Select the Apply to element rollup only checkbox and save. Next, select Apply
  7. Save and run the report to view the conditional formatting.
    The conditional formatting appears only for the rollup values for FY2014 in the columns and for PL Expense in the rows.
  • Was this article helpful?