Decision Functions in SSRS

SSRS supports 3 Decision Functions, all of which are extremely useful.

Switch Function

Switch is useful when there are three or more conditions to test because it’s simpler to read than nested IIF statements.  It's similar to a Case statement in t-sql.  A Switch will return the first expression that it finds true, so the order of the conditions is very important.

I've found the Switch function to be particularly useful in conjunction with conditional image or color display, such as which indicator to display based on a data value.  See this blog entry for an example of nesting a Switch function within an IIF function.



     Fields!StudentAbsenceCount.Value >= 5, "Red",

     Fields!StudentAbsenceCount.Value > 1, "Orange",

     Fields!StudentAbsenceCount.Value = 1, "Yellow",

     Fields!StudentAbsenceCount.Value <= 0, "Green") 

Choose Function

Choose is useful when you present the user with a parameterized list of choices which drive the value to be displayed in one or more data regions. 

This function may only be used in conjunction with a parameter, and it must have an integer value to associate with the parameter list the user sees (ex: Current Year Budget is 3rd in the parameter list; the 3 is what's actually passed to the Choose function).  Luke Hayler describes this well in his blog post



     “Actual-Current Year”,

     “Actual-Prior Year”,

     “Estimate-Current Year”,

     "Estimate-Prior Year")

IIF Function

An “immediate” IF function in the format of If, Then, Else.  Similar to Excel, multiple IIF’s may be nested.  This function is evaluated on a cell by cell basis.


     =IIF(Fields!StudentAbsenceCount.Value >= 1, "Red", "Green")

More information can be found here:  MSDN Expression Examples.