Applying conditional formatting to measures

Conditional formatting in the Analyzer data grid changes how cells look. Cells can change color or show icons based on their values. Analyzer includes built-in formatting options for numeric measures.

Apply conditional formatting to measures

1. Right-click a measure in the grid.

2. Select Conditional Formatting.

3. Select a conditional formatting type.

Analyzer refreshes and applies the formatting you selected.

Conditional formatting types

• Color scale: Shades the cell background based on the value range.

• Data bar: Fills the cell background proportionally to the value range.

• Trend arrow: Shows an up or down arrow for positive or negative values.

Apply advanced conditional formatting with MDX expressions

Use an MDX expression when the built-in options are not specific enough.

1. Right-click a measure in the grid.

2. Select Column Name and Format.

3. In Format, select Expression.

   Analyzer adds a default MDX expression:

   Case
   When [Measures].CurrentMember > 0
   Then '|#,##0|arrow=up'
   When [Measures].CurrentMember < 0
   Then '|#,##0|arrow=down'
   Else '|#,##0'
   End

4. Update the expression as needed.

   If the expression is invalid, Analyzer shows an invalid report definition error.

5. Select OK.

Analyzer refreshes and applies the formatting you specified.

Conditional formatting expressions

MDX format strings can include rendering properties for Analyzer HTML pivot tables.

Use this general pattern:

|#,###|style=red

The leading pipe (|) tells Analyzer the format includes properties. The #,### part sets the numeric display format. The style=red part is a key=value pair.

Analyzer supports these properties:

• style: Changes the cell background color.

  • Values: red, yellow, green

• arrow: Shows a trend arrow.

  • Values: up, down, none

  • Use none when you want only one arrow direction.

• link: Creates a link that opens in a new window.

  • Value: a fully qualified URL in quotation marks

• image: Renders a custom image.

  • Value: an image file name with extension

  • Store images in: /pentaho-solutions/system/analyzer/resource/image/report/

Need the original subpages? See Applying conditional formatting to measures (archive).