Stairway to SQL Server Reporting Service: Decision Functions

As part of expressions, decision functions can be used in calculated fields, filters, formatting to dynamically control related properties. They can be used with SQL, MDX, DAX query to query data from database, cube, SSAS tabular model respectively. This article will give a brief introduction and comparison of three decision functions: IIf, Switch, Choose. Also examples of their usage are given at the end.

IIf Function

IIf function returns one of two values depending on whether the expression is true or not. The syntax is as below:

IIf( <Boolean Expression>, [Return if True], [Return if False])

It likes conditional logic in SSIS. The first parameter is the expression to be evaluated. If it is true, then return the value specified in [Return if True]; If it is false, then return the later value specified in [Return if False]. You can have nested multiple IIf functions and the later condition is dependent on previous conditions' evaluation.

Switch Function

Switch function returns the value associated with the first expression in a series that evaluates to true. The statements will be evaluated in the order in which they appear and the condition expression can be unrelated with each other. The syntax is as following:

=Switch(
       <Boolean Expression 1>, [Return if True 1],
       [Boolean Expression 2], [Return if True 2],
    ...
       [Boolean Expression n], [Return if True n],
      )

Choose Function

Choose function uses a single numeric evaluation to determine which of return values to return. The syntax is as below:

Choose(
     <Numeric Expression>, 
     [Return Expression 1],
     [Return Expression 2], 
     …, 
     [Return Expression n],
)

Comparisons of Three Decision Functions

As you can see, Choose function is quite different from the other two. Since the return only depends on the numeric evaluation specified. IIf and Switch function can be used interchangeably for a certain condition, as example of dynamic text font color below shown. For me, for case conditions are more than three, Switch function has clearer logic thus is easy to follow. However, Switch and Choose functions may have possible undefined results while IIf function won't since IIf function requires that a “return if true” value and a “return if false” value be provided in the expression.  One way to avoid this is to specify the last condition in Switch function to be TRUE as the following example.

 =Switch(Fields!OrderNumber.Value >= 10, "Blue", 
        Fields!OrderNumber.Value >= 5, "Yellow", 
 Fields!OrderNumber.Value >= 2, "Orange", 
 TRUE, "Red")

 

Problems	Expressions
To set filter expression depending on Country Code from MDX query.	Solution 1: =IIF(Parameters!CountryCode.Value.ToString() <> "[Location].[Country Code].&[USA]", "4", "6")  Solution 2: =IIF(InStr(Parameters!CountryCode.Value .ToString(),"USA")>0, "6","4")
To format the report category based on input date parameters ReportDate.	=IIF( InStr("January, February, April, May,July,August,October,November", Monthname(Datepart("m", Parameters!ReportDate.Label)) ) > 0, "Monthly","Quarterly" )+ Space(1) + "Reports"
To dynamically change the color of a text box, go to properties, and set the following expression for font/Color Property by Switch function.	=Switch(  Fields!OrderNumber.Value >= 10, "Blue",  Fields!OrderNumber.Value >= 5, "Yellow",  Fields!OrderNumber.Value >= 2, "Orange",  TRUE, "Red")
To dynamically change the color of a text box, go to properties, and set the following expression for font/Color Property by IIf function.	=IIF( Fields!OrderNumber.Value >= 10, "Blue", IIF(Fields!OrderNumber.Value>= 5, "Yellow", IIF(Fields!OrderNumber.Value>= 2,"Orange", "Red" ))))