Examples Applying Conditional Formatting

Below are common usage examples of conditional formatting.

Indent Level

The IndentLevel Property filter will dynamically format from defined rows or expansions. Indentation is zero-based.  The formatting can be applied to the default or to rows.  This solution can speed formatting for summary level dimension members.

Apply the formatting to the cube view's Header section or to specific rows.

Examples of Using Conditional Property Filters Conditional “Traffic-Lighting”

“Traffic-Lighting” is data related and therefore applied as a cell format.  The designer has a choice to apply the conditional formatting to either a row or a column. The order of operations for formatting can impact the decision.  Row overrides are the final layer of formatting applied to a cube view and would not be impacted by other more general formatting.

1. Conditional formatting can be applied to the rows or columns.  A definition applied to the row Formatting tab would apply to all columns.  A row override would isolate the formatting to a specified column(s). 

2. The CellAmount filter is used within multiple If/ElseIf statements to define the various tests required for the report.

ExpandedRowNum Expansion Range

Conditional formatting provides the ability to format an expanded range of data cells using the ExpandedRowNum Property Filter.  This is useful in formatting to support “Top 10” type reports.  This filter best supports cube view designs that use known expansions, such as ranking business rules or member lists. If applied to specific rows, the formatting applies to the defined row, but the Expansion Number Reference relates to the entire cube view.  Formatting defined on subsequent rows will be impacted if the expansion members change on previous rows.  The ExpandedRowNum Property

Filter can also be applied as a default cube view format, in which the definition will apply to all rows. This would require If/ElseIf type statements to support all rows.

1. Design or open a report which supports formatting for ExpandedRowNum , such as a ranking report in the example.

2. Determine how the formatting should be applied, as a default, row or column.  The example will use the cube view default formatting.

3. Cell format is applied for conditional formatting using the ExpandedRowNum filter.  Being zero-based, and having to account for each row and its expansions:

  1. Condition1 – Rows < 6 is defined because the text header row “Top10Title” initiates the count at zero.

  2. Condition2 – Rows > 11 and < 15 is defined because all the rows up to Row4, “SalesBottom10”, reflect rows 0-11 on the cube view.  The conditional row references must reflect all the cube view expansions.

Test for Row IsRowNumberEven

The IsRowNumberEven property filter may be useful to vary formatting by the even/odd numeric expansion of rows on a cube view.  For example, to replicate a “green-bar” style report the IsRowNumberEven would be a suitable filter. 

In this example the formatting can be applied to the cube view default cell format since it will globally apply to all rows. 

MemberName, MemberDescription or HeaderDisplayText

The ability to format based on the Name or Description is greatly enhanced by adhering to a standardized metadata naming convention.  Summary level members having keywords such as “Total” or prefixes or suffixes such as “Tot” could be used in conditional formatting. Members could be formatted using dynamic criteria, such as StartsWith or EndsWith.

HeaderDisplayText differs from the MemberName and MemberDescription Property filter in that 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.

Apply conditional formatting to row header.

The result is a dynamically formatted report.

Parameter Formatting

Conditional formatting definitions can be applied to a cube view as a Literal parameter. To apply, select the format string from the cube view, press Ctrl-C, select the Default value property of a Literal Value-type parameter and press Ctrl-V. Once saved, this parameter can be referred to in a cube view format. As shown, the reference of |!Param_Conditional_PLTree!| in a cube view format would apply the associated format string.