SSRS Consultant Tip: Conditional Formatting
Anyone who’s in software development, and works directly with business users, knows that no matter how much things change, one rule remains:
You never get credit for what’s hard. You may, however, get credit for what’s pretty.
In that spirit, I’m going to write a few posts on making things prettier in SSRS. And today, I’m going to discuss conditional formatting.
By conditional formatting, I basically mean making things turn red when there’s a problem. I’m not talking about fancy charts. I’m talking about basic operational reports where you want users to know where there’s a problem.
For example, one of my clients uses Dynamics NAV. The “joy” of NAV is that it lets you enter data incompletely—and then only warns you when you post. That’s sub-optimal.
Instead, we’ve built messages in our reports to alert users before they post, such as when they miss a value or a transaction is not in balance.
Using Conditional Formatting to Highlight Errors
Let me illustrate with an example. Here’s a snip of a report in Visual Studio. It shows a simple text box with an expression:
Now, let’s look at the expression:
In this case, Invoice_Amount is the amount entered at the header level (which was a modification we needed in NAV). Sum is the sum of the expense distributions for the invoice. When they don’t equal, the user gets an error message (in red font).
Which was acceptable for the first couple of months. But users wanted more. They wanted it to be OBVIOUS. So, we decided to format the box itself.
We started with Text Box Properties:
From Text Box Properties, we chose Fill:
On the Fill page, we choose expression by clicking the function symbol:
If we look at the function, we see pretty much the same logic. If the amounts differ we want the box to be red. (Instead of changing the background color, we could have chosen font and created a similar expression.) But the client wanted the error message to be super obvious.
As for the report itself, we can see that—depending on whether the report balances or not—we get an error message on a red background.
Here’s the data, one invoice is in balance and one is out of balance.
And here’s the output:
As you can see, the first invoice is balanced (with no error message) and the second invoice is NOT balanced (with an error message).
A final word: Like many things, a little formatting goes a long way. Use this kind of formatting sparingly. If the report starts to look like a Christmas tree, the colors lose their impact.
Those of you guilty of this know who you are.