Skip to main content

Pentaho+ documentation has moved!

The new product documentation portal is here. Check it out now at docs.hitachivantara.com

 

Hitachi Vantara Lumada and Pentaho Documentation

Applying conditional formatting to measures

Parent article

Conditional formatting in the Analyzer data grid means that cells will be physically affected by the data they contain. The most common form of conditional formatting is stoplight reporting, where cell backgrounds are colored red, green, or yellow depending on user-defined thresholds. Analyzer offers some simple pre-defined methods of conditionally formatting numeric data.

Apply conditional formatting to measures

Follow the directions below to implement conditional cell formatting in your Analyzer report.

Procedure

  1. Right-click a measure in the grid, then select Conditional Formatting from the context menu.

    A sub-menu with conditional formatting types will appear.
  2. Select your preferred number format from the list.

Results

The Analyzer report will refresh and apply the formatting choice you specified.

Conditional formatting types

You can apply several conditional formatting types in your Analyzer report.

Indicator TypeDescription
Color scaleThe background cell color will be shaded according to the value of the cell relative to the highest and lowest recorded values in that measure. There are several color progressions to choose from.
Data barThe cell background is partially filled with a solid color proportional to the scale of the cell's value relative to the highest and lowest recorded values in that measure.
Trend arrowAn upward or downward arrow is displayed to the right of the cell value depending on whether it contains a positive or negative value.

Apply advanced conditional formatting with MDX expressions

If the premade conditional formatting options in Analyzer are not precise enough for your needs, you can apply custom formatting to a measure by using an MDX expression.

Procedure

  1. Right-click a measure in the grid, then select Column Name and Format from the context menu.

    The Edit Column dialog box appears.
  2. Select Expression from the Format drop-down list.

    A default MDX expression that prints green or red arrows in cells if their values are greater than or less than zero, respectively, appears in the MDX Format Expression field.
    Case
    When [Measures].CurrentMember > 0
    Then '|#,##0|arrow=up'
    When [Measures].CurrentMember < 0
    Then '|#,##0|arrow=down'
    Else '|#,##0'
    End
  3. Modify the expression to suit your needs. Consult Conditional Formatting Expressions for more information on conditional formatting syntax and options.

    NoteIf the MDX expression is invalid, an invalid report definition error appears at the top of the dialog box.
  4. Click OK to commit the change.

Results

The Analyzer report will refresh and apply the formatting choices you specified.

Conditional formatting expressions

Valid MDX format strings contain properties that apply special rendering for the HTML pivot tables. A format string follows this syntax:

| # , # # # | style = red

The leading Pipe ( | ) tells Analyzer that this format string contains properties. The '#,###' is the measure's value format, which in this example is one number separated from three further places or decimal places by a comma, for example, '3,667'. The 'style=red' string is a key/value pair; all possible keys and values are explained in the table below.

The following properties are supported by Analyzer:

Indicator TypeDescriptionValues
styleChanges the cell background color.
  • red
  • yellow
  • green
arrowShows a trend arrow that points up or down. A value of 'none' will not render the arrow, which is useful when you only want to show one directional arrow instead of two.
  • up
  • down
  • none
linkCreates an HTML link which will open in a new window when clicked in Analyzer.
  • Can be any browser-renderable URL.
  • It must be a fully-qualified URL.
  • The URL must be enclosed in quotation marks.
imageRenders a custom image that you specify. This image file must be stored in the /pentaho-solutions/system/analyzer/resource/image/report/ directory.An image file name, with extension.