Maybe I’m not the first one to figure this out, but I didn’t find anything when I was googling for an answer yesterday.
So I was trying to do some conditional formatting in Microsoft Reporting Services…in particular, I was trying to change the background color of a cell depending on what the value in that cell was. I could only find instructions on how to change the color for two possibilities. I had three possibilities, and needed three colors.
The instructions that I found said to use an expression like this in the background color property for the cell:
=IIF(Fields!Whatever.value=”Yes”,”Green”,”Red”) (If the “Whatever” field contains the value “yes” then make the background green, or else make it red.)
That didn’t work for my three color option, since IIF statements can only do two colors…one color if it’s true, one color if it’s false. Actually, now that I think about it, I probably could have used another IIF statement for the false value, but oh well, I like this solution more.
What I did was I added a case statement to my SQL query for each color. It looked like this:
CASE Some_Field WHEN ‘Yes’ THEN ‘Green’
WHEN ‘Maybe’ THEN ‘Yellow’
WHEN ‘No’ THEN ‘Red’
END AS ‘Color’
I then set my background color for the cell in the report to:
It worked perfect. I hope someone finds this useful.