SSRS - Using Conditional formatting (Background)

Question

I am new to Stackoverflow and I am fairly new to using SSRS and report builder. I am trying to change the background color based on values in 2 different cells, but it does not seem to work. I have tried using an Iif statement with a nested Switch statement, but this does not seem to work. I also tried using just a SWITCH statement, but that just gives me fields with no color at all.

What I am trying to achieve is that if the value in the "excav" column is "EX01", it must follow a different set of rules. Ways I have tried it:

=SWITCH(
    Fields![excav].value = "ex01" and Fields!tons.Value < 500 , "Red",
    Fields![excav].value = "ex01" and Fields!tons.Value >= 500 and Fields!tons.Value < 1000, "Yellow",
    Fields![excav].value = "ex01" and Fields!tons.Value >= 1000, "Green",
    Fields!tons.Value < 1840, "Red", 
    Fields!tons.Value  >= 1840 and Fields!tons.Value < 2300 , "yellow", 
    Fields!tons.Value >= 2300, "Green")

Result In this instance, it completely ignores the conditions for "ex01"

=Iif(SWITCH(
        Fields!excav.value = "ex01" and Fields!tons.Value < 500 , "Red",
        Fields!excav.value = "ex01" and Fields!tons.Value >= 500 and Fields!tons.Value < 1000, "Yellow",
        Fields!excav.value = "ex01" and Fields!tons.Value >= 1000, "Green"),
        SWITCH(
        Fields!tons.Value < 1840, "Red", 
        Fields!tons.Value  >= 1840 and Fields!tons.Value < 2300 , "yellow", 
        Fields!tons.Value >= 2300, "Green"),"No Color")

Result In this instance, it ignores all conditions and goes straight to the "No Color" condition.

Any Assistance would be greatly appreciated.

Try do it by the textbox name( ReportItems!TextboxName.Value) — Snowlockk
Usually there is no need to nest Iif and Switch. In your case I would prefer using only Switch like you do in the first code expression. FYI: If you want something like Else in your Switch expression just use True as your final condition. — niktrs
I have tried using it by the textbox name. It does not do any formatting now: =SWITCH(Fields![excav1].value = "EX01" and Fields!tons.Value < 500 , "Red", Fields![excav1].value = "EX01" and Fields!tons.Value >= 500 and Fields!tons.Value < 1000, "Yellow", Fields![excav1].value = "EX01" and Fields!tons.Value >= 1000, "Green", Fields!tons.Value < 1840, "Red", Fields!tons.Value >= 1840 and Fields!tons.Value < 2300 , "yellow", Fields!tons.Value >= 2300, "Green") — Diacide

niktrs niktrs · Accepted Answer · 2017-06-19T07:53:15

Propably it has to do with case, which means string ex01 is different than EX01.

So you have to either change the expression to Fields!excav.value = "EX01" or use a function like LCase to make the values lowercase and then compare

LCase(Fields!excav.value) = "ex01"

I have added the sample image to show you that it works if you follow either of my suggestions.

On the upper table I use your expression, while on the lower table I have used the LCase function

=SWITCH(
    Lcase(Fields![excav].value) = "ex01" and Fields!tons.Value < 500 , "Red",
    Lcase(Fields![excav].value)  = "ex01" and Fields!tons.Value >= 500 and Fields!tons.Value < 1000, "Yellow",
    Lcase(Fields![excav].value)  = "ex01" and Fields!tons.Value >= 1000, "Green",
    Fields!tons.Value < 1840, "Red", 
    Fields!tons.Value  >= 1840 and Fields!tons.Value < 2300 , "yellow", 
    Fields!tons.Value >= 2300, "Green")

SSRS - Using Conditional formatting (Background)

1 Answers