Perform the following steps to create a formula to highlight a given data cell with a background color depending the value of a field in your result set:
- Open an existing report or create a new report, establish a data source and query, then drag your data-driven fields onto the canvas.
- Select the data field you want to conditionally highlight.
- Click the Structure tab and click bg-color in the text section under the Style tab.
- Click the round green + (Add Expression) icon in the Formula column.
The Expression dialog appears.
- Click the ellipsis (...) to open the Formula Editor dialog box, then select Logical from the Category drop-down box.
- Double-click the IF statement in the list on the left.
- Click the Select Field icon, (on the far right), next to the Test line.
- 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.
- 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.
- 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.
- Click OK to exit the Formula Editor dialog box; click Close to exit the Expression dialog box.
- 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.