Skip to main content
Version Pentaho
Hitachi Vantara Lumada and Pentaho Documentation

Conditional Formatting

  1. Last updated
  2. Save as PDF

The formula described here will highlight a given data cell with either a red or green background depending on a string value from a field in your result set. You can easily modify these instructions to use different indicators or thresholds to match your preference.

  1. Open an existing report or create a new report and establish a data source and query, then drag your data-driven fields onto the canvas.
  2. Select (left click) the data field you want to conditionally highlight.
  3. Click the Structure tab and click bg-color in the text section under the Style tab.
  4. Click the round green + (Add Expression) icon in the Formula column. The Expression dialogue will appear.
  5. Click the ellipsis (...) to open the Formula Editor dialog box.
  6. Select Logical from the Category drop-down box.
  7. Double-click the IF statement in the list on the left.
  8. Click the Select Field icon, (on the far right), next to the Test line.
  9. In the Select Field box, choose the field you want to conditionally format, then click OK to return to the Formula Editor. Alternatively, you can simply type the field name in [square brackets] if you already know what it is.
  10. Add a conditional statement to the Test line, after your field name. This is one of your formatting conditionals. For instance if you wanted to highlight cancelled orders in red, and this field contained order status, you could put [STATUS]="Cancelled" in the Test line, then a color value for red in the Then_value line, as shown in the next step. 
    [STATUS]="Cancelled"
  11. In the Then_value line, type the color value or name you want to highlight this field with if the condition in the Test line is met. This can be a standard hexadecimal color value (such as #FF0000 for red), or a standard HTML color name (red, green, white, black, etc.).
    Note: This value must be in quotes.
  12. Click OK to exit the Formula Editor dialog box; click Close to exit the Expression dialog box.
  13. Click Preview and verify that your conditional formatting is properly executed. You may have to adjust your query if it does not produce a testable result set.
  14. Optional: To add more conditions -- such as to highlight both cancelled and disputed orders in red -- add an OR statement at the beginning of your Test line, enclose the conditions in parenthesis, and separate them with semicolons. 
    OR([STATUS]="Cancelled";[STATUS]="Disputed")
Your report output should now be formatted according to the specified conditions.

This is the resultant formula, following the above example for one condition and red and green colors:

Simple conditional formatting
=IF([STATUS]="Cancelled";"#FF0000";"#00CC00")

This is the resultant formula, following the above example for two conditions and red and green colors:

Multiple conditions
=IF(OR([STATUS]="Cancelled";[STATUS]="Disputed");"#FF0000";"#00CC00")