SQL Reporting Services Switch Statement

29 Aug

SSRS has a switch function, which mirrors the functionality of a Case statement, and can really save you some time.

Most common use for me are setting colors for parts of my report based on the value of a field.

For example, if we have a field: Orders (Fields!Orders.Value) which carries an integer representing the number of orders this week, we may want to set the background color of our report text box based on the order performance.
=Switch(Fields!Orders.Value < 100, "Red", Fields!Orders.Value < 500, "Green")
In the above example, if orders are < 100, we return Red, if they are 100 to 499, we return Green.

How does this work?

The switch statement takes an expression, followed by the return value if that expression evaluated to true. Additionally, it will always take the first expression that evaluated to true, that is why it’s OK that a value of 40 (for example) would satisfy both cases. 40 will always give us red, because it’s the first expression satisfied.

If you want a ‘default return value’, just take advantage of the fact that the first true expression is evaluated, and modify your code like this:
=Switch(Fields!Orders.Value < 100, "Red", Fields!Orders.Value < 500, "Green", 1=1, "Black")
Now, if our value doesn’t satisfy the other expressions, we know the last expression will evaluate to true (1 always equals 1), hence “Black” is our default return value.

You may want to check out one of my favorite SSRS books on Amazon.

3 Replies to “SQL Reporting Services Switch Statement

  1. Hi,
    I have a report that displays hours per day. The current font/color settings are set up for text as follows:
    =IIf(ReportItems!hours1.value <= 0, "Red", IIf(ReportItems!hours1.value 4, “SeaGreen”,”Black”)))
    However, the user wants Sat & Sun hours to be “Black” and blank if there are no hours. The “blank” part works, The report query uses “day_num” for the days of the week (1 = Sun & 7 = Sat). The expression that the report uses for formatting the “hours” column is:
    =iif(fields!day_num.Value = “7” or fields!day_num.Value = “1”,iif(Sum(Fields!hours.Value)>0,format(sum(fields!hours.Value),”#,##0.0;(#,##0.0)”),””),format(Sum(fields!hours.Value),”#,##0.0;(#,##0.0)”))
    I have tried all kinds of combinations, but can’t seem to come up with the right one. If youcan help, I would appreciate it.

Leave a Reply

Your email address will not be published. Required fields are marked *