In a prior post, I demonstrated how to use conditional formatting techniques to change the background color of every other row in tabular report. In that example, I used the Immediate If (IIF) function to change the BackgroundColor property of the odd rows to silver.
The IIF function only allows for two states. In my example, the odd rows were silver and the even rows had a white background color.
In the comments section of that post, one reader, Wally, asked to have more than two states, silver, white, and red. Another reader, Darin, replied that you can nest your IIF statements. You can read the comments here. Thanks, Wally and Darin! I love with people comment to posts, especially when it's in the spirit of helping each other.
Nesting IIF functions is commonly done. I've done it many times. However getting more than three states can get rather messy. You have multiple IIF functions nested, each with its own set of commas and parenthesis. It's very easy to mistype something and have the whole thing not work.
Fortunately, SSRS supports other VB functions that we can use to create more complex conditional formatting settings.
Let's consider a rather simple Sales Report as an example. In the following report, we have a report with three columns of information - the Sales Order Id, the Order Date, and the Sub Total for the Sales Order.
Now let's say that our requirements for the report dictate that the Sub Total amount must be shown in a different color depending on it's value. If the Sub Total is < $1,000, the figure should be shown it red. If it's between $1,000 and $10,000, the figure should be displayed in blue. Between $10,000 and $30,000, in green. And finally, all Sub Totals over $30,000 should be in black. Yes, I know that these requirements may not make much business sense, but such is the case with some requirements.
Anyway, to implement this more complex formatting requirements, we can turn to another VB function, the Switch function. The Switch function takes a series of argument pairs. It returns the first value immediately after the first condition statement that evaluates to true. It may sound confusing, but it's really rather straightforward.
To implement our requirements, select the Sub Total textbox. In the Properties window, find the Color property and select <Expression...> from the dropdown list.
In the Edit Expression window, enter the following function.
The Switch function will return Red, Blue, Green, or Black depending on the value of Fields!SubTotal.Value. The first expression to evaluate to true will be returned.
Press Ok to save the expression and Preview the report.
The Sub Total column changes the Color property according to our requirements.
I hope you find this useful. Let me know in the comments section if you've found some other particularly useful VB functions to conditionally format a report.
Cheers!
Joe
