SSRS - Conditional formatting not getting applied?

Question

I have the following expression to apply background colour to a text box but only the red colour is getting applied when that condition is true. All the other conditions are showing up as white? For example when the first condition is true when the report renders, the background colour is white instead of green?

=IIF(Round(SUM(CInt(Fields!TotalAchieved.Value) * 7.14)) >= 86, "Green",
IIF(Round(SUM(CInt(Fields!TotalAchieved.Value) * 7.14)) >= 79 AND 
Round(SUM(CInt(Fields!TotalAchieved.Value) * 7.14)) <= 85, "Light Green",
IIF(Round(SUM(CInt(Fields!TotalAchieved.Value) * 7.14)) >= 64 AND 
Round(SUM(CInt(Fields!TotalAchieved.Value) * 7.14)) <= 78, "Yellow", "Red" )))

Answer 1

It would seem that your SUM(CINT(Fields!TotalAchieved.Value) * 7.14) calculation is not giving you the results you expect . The first thing I would do is add a column that shows this value to make sure that it's gives you what you expect.

Once you have that correct then I would also suggest that you use a SWITCH statement rather than nested IIFs, they are much easier to read/debug.

You expression would be

=SWITCH(
Round(SUM(CInt(Fields!TotalAchieved.Value) * 7.14)) >= 86, "Green",
Round(SUM(CInt(Fields!TotalAchieved.Value) * 7.14)) >= 79, "LightGreen",
Round(SUM(CInt(Fields!TotalAchieved.Value) * 7.14)) >= 64, "Yellow",
TRUE, "Red" 
)

This way you don't need to check for ranges as, for example, if the value was 75, the 1st expression fails but the second one is true so SWITCH will stop at that point and not evaluate the rest, if all fail then the final TRUE will act like an else.