Configuring Background Colors Based on Multiple Factors

In this article I’ll demonstrate how to change the background color of cells based on multiple conditions.

First, I’ll start with a series of currency values.

As you can see, the values will vary widely.

The user of this report will receive many pages with these types of values and need the ability to quickly notice certain values based on their amount.

For example, they need those which equal zero and below zero to visually display differently than all other values.

I’ll update this field’s background property in report designer as follows:

• If value=0, set background color to yellow.
• If value<0, set background color to red.

First, I’ll access the field’s textbox background color in properties.

Next, I’ll click the “fx” expression button.

In the Expression editor, I’ll use two “IIF” statements to instruct the reporting engine how to set each cell’s background color depending on the current value.

Each “IIF” statement has the following format: IIF([condition], [true], [false]).

As you can see, the first “IIF” statement means “if the value is less than zero, set the color to red.

If the value is not less than zero, then evaluate the second “IIF” statement which determines if the value is equal to zero, set the color to yellow, otherwise (for all other values), set it to white (normal).