SWITCH function in SSRS - Equivalent of CASE statement

SQL Reporting Services Switch Statement

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 < class="str">"Red", Fields!Orders.Value < class="str">"Green")

In the above example, if orders are < style="color: rgb(255, 0, 0);">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, becuase 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 < class="str">"Red", Fields!Orders.Value < class="str">"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.