Create Conditional Formatting

Conditional formatting provides the ability to format headers or cells based on defined criteria. Conditional formatting in cube views supports data analysis by highlighting cells or ranges of cells, identifying key values, and using data bars, color scales, and icon sets that correspond to specific variations in the data. Conditional formatting is applied and visible in data explorer, Excel, and report views. The conditional formatting criteria are applied to cube view rows or columns on the formatting property within the cube view editor.

Conditional formatting is available on the formatting elements of a cube view.

• Cube view default formatting

• Row and column headers

• Row and column data cells

• Row and column overrides

Conditional formatting follows the cube view processing order of operations:

1. Number format as defined in application properties

2. Cube view default

3. Column formatting

4. Row formatting

5. Column overrides

6. Row overrides

The combination of formats and overrides equals the format for the cell when rendered. Formatting can be applied and isolated to the nested expansion levels on rows and columns by using property filters in the dialog box. This feature is a cube view formatting option.

NOTE: If there are any existing formats before applying conditional formatting, they will be retained if the range of cells containing the conditional formats does not meet the conditions of the rule. All styles from the cube view and the selection styles that had been applied to that range are overridden by conditional formatting.

Conditional Formatting Properties

General and conditional formatting can be applied in the Header Format and Cell Format dialog boxes. The Condition and Format buttons launch additional dialog boxes with available options. The dialog box for conditional formatting is divided into sections.

1. Conditional statement: Defines the options for conditional statements to apply constraints to formatting.

2. Filter: Applies the criteria that pertain to your statement.

3. Operator: Contains criteria filters to use in your conditional statement.

4. Text/object: Find the text or object to be tested.

Header Property Filters

Header Property Filter	Description
IsRowHeader	Boolean, determines if an object is a row header field (appropriate for Cube View Default).
IsColHeader	Boolean, determines if an object is a column header field (appropriate for Cube View Default).
RowName	Cube view row name
ColName	Cube view column name
ExpansionLevel	Determines the expansion level for rows (1-4) and columns (1-2). Related to isolating the row expansion headers.
HeaderDisplayText	Custom descriptions used with the :Name() function
MemberName	Metadata member labels
MemberDescription	Metadata member descriptions
MemberShortDescription	Metadata member short descriptions (only Time dimension)
IndentLevel	Indentation level derived as a formatting setting or that is system-generated based on tree expansions

Expansion Specific Property Filters

Expansion Specific Property Filter	Option	Description
RowE expansion level - criteria	Row expansions 1–4	Identify nested or expansion rows with specified criteria
ColE expansion level - criteria	Column expansion 1 and 2	Identify nested or expansion columns with specified criteria

Cell Format Property Filters

Cell Format Property Filter	Description
IsNoData	Test for no data
IsRealData	Test for stored data, ignoring derived Zero-View data
IsDerivedData	Test for derived data, commonly resulting from Scenario Zero-View settings
IsRowNumberEven	Test for the row number as an expansion or on a fixed row
ExpandedRowNum	Test the count of expanded rows, Zero-based. This property filter is based on the total cube view count of rows generated from each row and its expansions.
CellAmount	Test cell data amount
CellStorageType	Test the method used to store data

Define Properties for Conditional Formatting

Conditional formatting can be applied based on a data point. Cell formatting conditions can occur based on member names or descriptions, indent levels, row and column names, and expanded and even numbered rows.

Conditional formatting can also be applied based on members. The ability to format based on the name or description is enhanced by using a standardized metadata naming convention. Summary level members with keywords such as “Total” or prefixes or suffixes such as “Tot” could be used in conditional formatting. Members can be formatted using dynamic criteria, such as StartsWith or EndsWith. HeaderDisplayText differs from the MemberName and MemberDescription property filter because it references the custom name parameter in a member filter. Dynamic criteria can be applied to the name and description property filters to apply the required formatting.

1. On the Cube Views page, under Cube View Groups, select a cube view.

2. Select Rows and Columns to expand the slider.

3. Select a row or column.

4. Click the Formatting tab.

5. Click the ellipsis to launch the dialog box.

6. Click Condition, set the requirements, and then click OK.

7. Click Format, set the requirements, and then click OK.

8. Click Condition and then End If to close the condition.

9. Click OK twice.