Switch expression for conditional formatting in SSRS 2008 R2

Question

I have this conditional formatting expression (for background colour) which works fine:

=SWITCH(
Fields!Fire_Safety.Value = "F", "LimeGreen",
Fields!Fire_Safety.Value = "i", "Red",
Fields!Fire_Safety.Value = "E", "Yellow",
TRUE,Nothing
)

I have now changed the data table (via SQL) so that where the “F” was – there are now dates.

I changed the Switch expression (in SSRS) to this:

=SWITCH(
Fields!Fire_Safety.Value <= dateadd("d",90,Today()),"Orange",
Fields!Fire_Safety.Value >= dateadd("d",91,Today()),"LimeGreen",
Fields!Fire_Safety.Value  = "i", "Red",
Fields!Fire_Safety.Value = "E", "Yellow",
TRUE,Nothing
)

And it fails. The date fields seem to work, but not the “i” and the “E”.

I guess it must be the syntax, but I’m a bit lost... Any ideas?

Check your output(CTRL+ALT+O), is returning any error message? — mxix
Considering you are comparing Fields!First_Aid.Value to dateadd("d",90,Today()), then I assume First_Aid is a date, in which case you cannot compare it to "i" or "e". I suspect you want to be comparing Fire_Safety with "i" and "e" in your 3rd and 4th conditions. — GarethD
@mmix yes I get this error "Conversion from string "i" to type 'Date' is not valid". So I guess this means Switch can do text or dates, but not both? — ttratl

GarethD GarethD · Accepted Answer · 2015-02-02T13:27:48

In a comment you have stated:

So I guess this means Switch can do text or dates, but not both?

No. In the predicate you can use any data type you like, consider this simple example:

=SWITCH(
    "A" = "B", 1,
    1 = 0, 2,
    TRUE, 3)

The fact that the first expression compares strings, and the second compares integers is irrelevant, all that really matters to the switch function is the result of the predicate, i.e. the above example is no different to:

=SWITCH(
    FALSE, 1,
    FALSE, 2,
    TRUE, 3)

The actual problem comes from the following expression:

Fields!Fire_Safety.Value  = "i"

And you would get an error whatever it was embedded in.

=IF(Fields!Fire_Safety.Value  = "i", 1, 0)

Would give the same error.

The problem is that since Fields!Fire_Safety.Value is a date, in order to compare a date to a string, the string must be first converted to a date, and "i" cannot be converted to a date, therefore you get an error. If your column only contains dates, the 3rd and 4th conditions can never be true anyway so they may as well be removed.

Switch expression for conditional formatting in SSRS 2008 R2

1 Answers