Creating a Condition

In order to get started with conditional formatting, text and/or conditional suppression, you first define the condition on the Conditional Properties panel. For more information about the criteria and parameters available for each conditional format or suppression type, see Conditional Formatting, Text and Suppression Criteria and Parameters.


When defining a condition that tests for data values, for example, Cell Value, Row Value, Column Value or Current Cell Value, in Grid Properties, you specify if conditional formatting and suppression uses scaled or unscaled raw data values within a condition using the grid property Use scaled/rounded value.

  • The default is True, which means that the formatted and scaled data values are used within the condition. For example, if a cell with a data value of "125,016,551" is scaled to thousands and displays as "125,016", the "125,016" value will be used within the condition.

  • If the property is set to False, the underlying and unscaled data value will be used within the condition. This setting applies to all conditional formatting and suppression for the grid.

Use scaled or rounded value

To define a condition:

  1. Select the area of the grid that you want to add a conditional format (cells, rows, or columns) or conditional suppression (rows or columns) to.

  2. In Grid Properties, click conditional properties.

  3. Perform an action:

    • To create a conditional format and/or text, click Formats/Text, and then click add.

    • To create a conditional suppression, click Suppressions, and then click add..


    When performing suppression on rows or columns for zero, no data (#missing), or error values, it is recommended to use basic suppression and not conditional suppression for better performance and efficiency. Basic suppression can be applied to the entire grid or specific rows and columns.


    When using Conditional Suppression on a row or column segment(s) where there is a single conditional expression, such as Data Values in Current Row/Column > 1000, and if you also have Basic Suppression such as Suppress No Data enabled, then you must modify the Conditional Suppression definition to include a conditional expression for Basic Suppression applied, such as Data Values in Current Row/Column = No Data. This applies to all the Basic Suppression types (No Data, No Access, Zero, and Error).

  4. Enter a descriptive name for the conditional format/text or suppression in the Name field. The name must be unique for the grid.


    You do not need to enter anything in the Applies To field. After you create the condition, the Applies To field will list the areas of the grid that you identified in step 1. You can modify the values in the Applies To field to change the area of the grid that you want the condition to apply to.

  5. In the expression builder, click Attribute and select an attribute that you want to test for. For example, you can test on the current cell value, or a member name.

    After you selected an Attribute, the Qualifier and Operator fields will change to reflect the available parameters.

  6. If applicable, specify a Qualifier. For example, if you selected Member Name for the type, you then enter the name of the member that you want to test for as the qualifier.

  7. Select an Operator.

    • For numerical conditions (data values), use a numerical operator (equal to, not equal to, greater than, less than, and so on.).

    • For textual conditions (for example, Member Name) use a textual operator (starts with, ends with, and so on).

  8. Select or enter a Right Qualifier, which is the condition that you are testing for.

    • For numeric qualifiers, you can use delimiters and the negative sign (-). All other prefixes and suffixes are ignored.

    • For textual qualifiers (such as Member Name), manually enter the text string that you want to test for.


      Member Selector is not available in the expression builder.

  9. Optional: Click add to insert additional expression condition rows.

    Use AND or OR to combine or differentiate the conditions. For example, you can create a condition that tests for data values greater than or equal to 100 AND less than or equal to 1000.

    Click delete to remove an expression condition row.

  10. Formatting/Text Only: In the right panel:

    • In the Format tab, specify the format (for example, font size, border, and shading) for the condition. Click # to open the number formatting menu. See Formatting Numbers in Formatting Grid Cells.


      In the conditional formatting panel, if any format fields are not changed or defined (for example, font styles, number formatting, and so on), the affected grid cells will use the cell formatting defined in the Grid Cell properties for the undefined fields and not the undefined field in conditional formatting. For example, in conditional formatting, under number formatting, if a negative prefix and suffix are not changed or defined, the affected grid cell will use the negative prefix and suffix defined in the Grid Cell properties.

      For Narrative Reporting deployments in Note Column and Note Row segments, you can restrict or allow Note entry via the Allow Note Input formatting property for specific cells in a Note segment. For more information, see Setting the Allow Note Entry Property.

    • In the Text tab, define the text to display. You can manually enter static and insert one or more text functions using the function button button. You can also format the text using text object button, which formats all the returned text, as text cells cannot display mixed formatted content, only one format can be applied to all the text.


    Regarding formatting for Conditional Text:

    If the ConditionalText function is inserted into a grid cell, the formatting defined for the Conditional Text will always override the cell formatting in the grid or report output.

    If the ConditionalText function is inserted into a text box:

    • In the Conditional Text Format dialog, if Use Default is not selected and a format is defined, even if formatting is defined for the ConditionalText function in the text box, the formatting defined for the Conditional Text will override the text box formatting in the report output.

    • In the Conditional Text Format dialog, if Use Default is selected, and formatting is defined for the ConditionalText function in the text box, the text box formatting will be used in the report output.

    The format and/or text that you specify is displayed in the sample at the top of the Conditional Formats/Text panel.

  11. Optional: Select (or multi-select) an expression condition row, and then right-click or select Action to group, remove, duplicate, or move them up or down.

    For more information on grouping expressions, see Working with Advanced Conditions

  12. Click OK to save the condition.

    The condition is displayed in the Conditional Properties panel. For formats, the sample formatting is also displayed, for text, a portion of the text definition is displayed.