Share via

SSRS Expressions: Part 1 - Program Flow

  • Article

Introduction

This is the first in a series of TechNet Wiki articles on SSRS expressions. SSRS expressions are a convenient and powerful means of extending the capabilities of your report. Dynamic decision making is one of the more powerful extensions you can add to a report. In a limited fashion, it is artificial intelligence. The Program Flow expressions in expression builder are the dynamic decision engines in SSRS.

Overview

There are three different expressions available to control program flow in SSRS expressions. They are: Choose, IIf, and Switch. Each evaluates its input and returns one or more possible values based on that evaluation. They all share certain traits in common but each has its own unique characteristics that make it uniquely suited for certain uses.

Program Flow Expressions

Let’s examine each of the three Program Flow expressions beginning with the simplest decision engine, the IIf statement, and ending with the Choose statement.

IIf Statement

The IIf statement is a basic decision engine. It evaluates a Boolean expression to either True or False and returns one of two values based on that evaluation. For programmers, it is the equivalent to an “IF, THEN, ELSE” statement.

Syntax

The IIf statement has three elements as described by the following syntax:

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

Boolean Expression

There is a virtually unlimited number of expressions of varying complexity that may be used. From a simple Boolean field or parameter, to a series of Boolean expressions joined using “And” and “Or” operators. Regardless of how simple or complex you make it, it must always evaluate to a single Boolean value, either True or False.

Return Expressions

The last two elements of the IIf statement are the return expressions. The first of these, the middle element of the IIf statement, is returned when the Boolean expression evaluates to True. The second of these, the last element of the IIf statement, is returned when the Boolean expression evaluates to False. Any valid SSRS expression may be used for either of these elements.

Discussion

A return expression could be a simple value such as “yes”, “Yellow”, or 12. It may be more complex such as another SSRS expression. It is not uncommon to find another IIf statement embedded in either the True expression or the False expression, or both. The benefits of embedding expressions within expressions must be weighed against the complexities and costs that such a practice introduces.

Examples

Simple IIf statement:
The following statement evaluates a Boolean field from a dataset and returns an appropriate text value.

=IIf(Fields!IsNew.Value, “Less than 30 days”, “30 days or more”)

IIf statement with comparison Boolean expression:
The following statement evaluates a non-Boolean field by comparing it to a known value and returns an appropriate value.

=IIf(Fields!RecordCount.Value >1, “A lot”, “One”)

Switch Statement

The Switch statement is used to evaluate several individual Boolean expressions and return a specified value when a particular expression evaluates to true. It is similar to embedding an IIf statement in another IIf statement’s return element. For programmers it is similar to an “IF, ELSE IF, ELSE” statement.

Syntax

The Switch statement is comprised of a virtually unlimited number of clauses* as described by the following syntax:

Switch(
*   <Boolean Expression 1>, <Return if True 1>,
*   <Boolean Expression 2>, <Return if True 2>,
   …, …,
*   <Boolean Expression n>*, <Return if True n>
)

*Each of the lines above consists of a Boolean expression and a value to be returned when the Boolean expression evaluates to true. This what I am referring to as a clause.

Boolean Expression

The Boolean expression in the switch statement is identical to that in the IIf statement. While not a requirement, the different Boolean expressions in a Switch statement are often related, usually by comparing the same field or parameter to different values.

Return Expressions

Just like the IIf statement, the return expression may be any valid SSRS expression. Unlike the IIf statement, the switch statement only specifies a value to return when the associated Boolean expression evaluates to true. There is no “return if false” clause.

Discussion

The clauses of a Switch statement are executed in sequence. Once an expression evaluates to true, the associated return expression is returned and the Switch statement execution stops. If none of the Boolean expressions evaluate to true, then nothing is returned. This may not always be desirable.

Because the Switch statement evaluates each Boolean expression in sequence, it is possible to handle all other cases by adding a final clause. For programmers, this would be the equivalent of the ELSE clause of an “IF, THEN, ELSE” or “IF, ELSE IF, ELSE” statement. Add a final clause to the Switch statement that always evaluates to true, like: “True, <Return if True>”. Since it is the last Boolean expression, it is only evaluated when all other Boolean expressions evaluate to False. Since it always evaluates to True, the Switch statement will always return a value.

Example

Simple Switch statement:
The following example returns the textual “type” for the TypeID field of a dataset.

=Switch(
     Fields!TypeID.Value = 1, “chair”,
     Fields!TypeID.Value = 2, “sofa”,
     Fields!TypeID.Value = 3, “loveseat”,
)

Choose Statement

The Choose statement uses a single numeric evaluation to identify which of its multiple return values to return.

Syntax

The Choose statement allows for a virtually unlimited number of return values based on a single numeric input as described by the following syntax:

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

Numeric Expression

Choose only understands an Integer value for the numeric expression but it is able to convert from a Decimal to an Integer on the fly by ignoring all digits to the right of the decimal place.

Return Expressions

Like the IIf and Switch statements, the return expressions in a Choose statement may be any valid SSRS expression.

Discussion

Choose evaluates the numeric expression as an Integer and returns its nth value where n is the Integer that the numeric expression evaluates to (i.e. 3.14159 would evaluate to 3). The first return expression corresponds to 1, the second to 2, and so on. If the numeric expression evaluates to a value higher than the available number of return values, nothing is returned. In this way it is like Switch.

Since Choose evaluates the single Integer expression, there is no means to insert a “catch-all” into the Choose expression as described for the Switch statement. Also, Choose expects sequential integers ( i.e. 1, 2, 3, …, n). As a result, The Choose expression is best used for situations where there are a fixed number of known, sequential Integers that will result from the numerical expression.

Example

Simple Choose expression:
The following example returns the textual equivalent of the integer key.

=Choose(Fields!Key.Value, “one”, “two”, “three”, “four”)

Comparing Characteristics of Program Flow Expressions

The Program Flow expressions all share certain common characteristics and they all have unique characteristics that make them uniquely suited for certain applications. The below table describes the various possible characteristics of the Program Flow expressions and indicates which characteristics apply to which expressions.

 

IIf

Switch

Choose

Type of Decision

Boolean

Boolean

Integer

Returns One Value

X

X

X

Possible Undefined Result

X

X

Returns False Value

X

Returns True Value

X

X

X*

May Evaluate Multiple Conditions

X

X*

Can Handle All Possible Decision Results

X

X

* See “Type of Decision” section below for explanation of the integer decision engine used in the Choose statement and how it can be related to a series of Boolean evaluations.

Understanding the capabilities of each of these expressions will help you to choose the appropriate one for your needs.

Type of Decision

Decision making can be distilled down at its root level as a series of Boolean evaluations. The IIf and Switch expressions expose the Boolean evaluation in the command syntax. The Integer decision engine utilized by the Choose expression can be conceptualized as a series of related Boolean expressions (x=1, x=2, …, x=n) that are evaluated in sequence, in ascending order. Thinking of it in this way can make it easier to compare the three Program Flow expressions.

Even so, the mechanism for implementing the decision-making process in each expression imparts certain advantages and disadvantages to each. IIf can only evaluate a single expression and therefore has only one true condition while switch allows for an unlimited set of evaluations, related or not, which allows it to return any one of a number of possible true results. Choose is similar to Switch with several additional restrictions that are inherent in the Integer decision engine but it too can return any of several true results.

Returns One Value

All of the Program Flow expressions return only one value. IIf will return either the true value or the false value, not both. Switch will return only one of the available true values, as will Choose. The section on Overcoming Weaknesses below addresses this in more detail.

Possible Undefined Result

An IIf expression has a single Boolean expression that will evaluate to either true or false. IIf requires that a “return if true” value and a “return if false” value be provided in the expression so there is a result defined for all possible evaluations of the Boolean expression.

It is possible with both Switch and Choose statements for a condition to evaluate to a value that does not have an associated return value. Since Switch only returns a value when a condition evaluates to true, it is possible to have an undefined result when all conditions evaluate to false. Choose is similar to Switch. Since Choose returns a value based on the integer value of a number, it is possible that the number will evaluate to a value that is outside the scope provided for in the expression. Any negative number and zero are outside of the scope of a Choose expression since the first return value is always associated with the integer 1. And, if five return values are defined in the Choose expression then a value of 6 or above will also be undefined. Both Switch and Choose will return null (Nothing) for undefined values.

Returns False Value

IIf is the only Program Flow expression that defines a return value for a false condition. Switch has a default behavior to evaluate the next expression when the prior expression is false but does not return a value for the false condition. If we look at the evaluation of the numeric expression in the Choose expression as a series of Boolean comparisons as described above, it behaves the same as Switch. When the key does not evaluate to 1, it then compares the key to the next possible sequential integer, 2. The false condition is undefined for both Switch and Choose.

Returns True Value

All Program Flow expressions define values to return when the Boolean expression returns true (refer to Type of Decision for explanation of Choose expression Integer evaluation).

May Evaluate Multiple Conditions

IIf evaluates a single Boolean expression that may be a composite of multiple expressions joined using logical operators like “And” and “Or” but it is just one expression that returns only one of two possible values.

Switch allows the report designer to define as many Boolean expressions as needed. These expressions may be related, such as evaluating the same item against several possible values, or they may be completely unrelated.

The Choose expression’s Integer evaluation engine is the equivalent of evaluating multiple related conditions.

Can Handle All Possible Decision Results

Since the IIf expression does not have a possible undefined result, it handles all possible decision results. Both Switch and Choose may have undefined decision results. Because Choose is an Integer evaluation and the set of Integers are an infinite set, it cannot handle all possible decision results. Switch is unique in that it can have undefined decision results but it can also handle all possible decision results as described in the Discussion section of the Switch Statement above.

Extending Program Flow Expressions

Often, the methods employed to extend the Program Flow expressions use other types of expressions to offset the weaknesses or extend the strengths. Since this is only the first article in a series, it will be necessary to establish a basic understanding of certain other expressions. A more detailed description will be included in later articles.

Overcoming Weaknesses

The limitations of an expression may, in some cases, be overcome by leveraging other expressions or capabilities in SSRS. It just takes a little imagination and a strong understanding of the tools you have to work with.

Single Return Value

One common weakness in the Program Flow expressions is that each of these statements can return only one value, regardless of the number of expressions that might be evaluated. This cannot be changed, however SSRS supports arrays in various ways such as multi-value parameters or the Split function. Arrays offer the opportunity to return an array of values in lieu of a single value.

Example. Suppose I want to set the color palette of my report from dynamic user input. I can create a visible, Integer parameter, called Palette, with 3 possible values: Primary (1), Secondary (2), and Other (3). I can then create a multi-value parameter called Colors. The available values and the default values of the Colors parameter are set the same so that when a new selection is made for Palette, the old colors are no longer allowed causing the new default values to be applied. Colors is set using the following expression for both default and available values:

=Switch(
      Parameters!Palette.Value=1, Split("Red|Yellow|Blue",”|”),
      Parameters!Palette.Value=2, Split("Purple|Green|Orange",”|”),
      Parameters!Palette.Value=3, Split("Brown|Magenta|Cyan",”|”)
  )

Note that Split is used to convert the pipe-delimited string into an array. I could alternately define an internal multi-value parameter for each palette with the default values set to the colors to be used in the palette and use those in lieu of Split:

=Switch(
      Parameters!Palette.Value=1, Parameters!Primary.Value,
      Parameters!Palette.Value=2, Parameters!Secondary.Value,
      Parameters!Palette.Value=3, Parameters!Other.Value
  )

The advantage of this approach is there will only be one place to go to alter the colors used in a palette.

Since I set the values of the Palette parameter to sequential integer values beginning with 1, I could use Choose for an even simpler expression:

=Choose(
      Parameters!Palette.Value,
      Parameters!Primary.Value,
      Parameters!Secondary.Value,
      Parameters!Other.Value
  )

I can now set the colors used in the report using expressions. When Palette is set to “Primary”, anywhere I specify the expression “=Parameters!Colors.Value(0)” for the color, it will render in Red. Likewise, “=Parameters!Colors.Value(1)” will render Yellow and “=Parameters!Colors.Value(2)” will render Blue. If I change the Palette to Secondary, the same items will render Purple, Green, and Orange respectively.

We have leveraged the strength of arrays to overcome the limitations of the Program Flow expressions.

Undefined Results

Both Switch and Choose may return an undefined result if all conditions resolve to false. For Choose, that means that the condition evaluates to an Integer that is beyond the scope of the expression. To handle all possible results of the condition expression(s), we must handle both true results and false results. The nature of these two Program Flow expressions requires a different solution for each in order to resolve this issue.

Switch

Because we can set multiple, unrelated condition expressions and because the statements are evaluated in the order in which they appear, it is possible to handle all possible results in a Switch statement. Each condition expression in a Switch statement returns a specified result when the condition evaluates to true, so the true results are handled. When a condition evaluates to false, Switch passes execution to the next condition in sequence, thus handling the false result. An undefined result is returned when the last condition evaluates to false because there is no subsequent condition to pass execution to. The simple solution to this issue is to add a clause to the end of the execution sequence whose condition always evaluates to true and returns a default value. This clause effectively checks for “all other conditions are false” and so is a catch-all. In a simple form, a Switch statement implementing a catch-all clause might look like (catch-all clause is bolded):

=Switch(
      Condition1=True, “a”,
      Condition2=True, “b”,
      Condition3=True, “c”,
      True, “z”
)

Choose

Unlike Switch, Choose does not offer much flexibility when it comes to the condition expression. It must be a single numeric expression that can be evaluated as an Integer and it will be compared to the set of positive Integers in ascending order. This excludes the approach used above to handle undefined results in the Switch statement. In fact, there is no native means of handling these undefined results in a Choose statement so we must take a different approach.

One method is to wrap the Choose in an IIf statement and use a separate Boolean expression to check for any values not explicitly handled by the Choose statement. It is not an elegant solution but IIf will handle false results and so can mitigate the lack of this capability in Choose. The results can be difficult to read and maintain.

This example uses a complex Boolean expression to validate the Choose statement’s key value:

=IIf(
      Parameters!Palette.Value>0 And Parameters!Palette.Value<4,
      Choose(
                Parameters!Palette.Value,
                Parameters!Primary.Value,
                Parameters!Secondary.Value,
                Parameters!Other.Value
        ),
      “Black”
)

This solution is not too difficult to manage especially when you see it laid out as shown above. It is a little more difficult to read as it is likely to be seen in expression builder:

=IIf(Parameters!Palette.Value>0 And Parameters!Palette.Value<4, Choose(Parameters!Palette.Value, Parameters!Primary.Value, Parameters!Secondary.Value, Parameters!Other.Value),“Black”)

The next example uses IsNothing to check for a null result from the Choose statement:

=IIf(
      IsNothing(
            Choose(
                Parameters!Palette.Value,
                Parameters!Primary.Value,
                Parameters!Secondary.Value,
                Parameters!Other.Value
            )
      ),
      “Black”,
      Choose(
                Parameters!Palette.Value,
                Parameters!Primary.Value,
                Parameters!Secondary.Value,
                Parameters!Other.Value
        )
)

While it will work, it will quickly get unmanageable as your Choose statement grows. The effect of any complexity in the Choose statement is double on the full expression since the same Choose statement is repeated twice; once to check for an undefined return value and once to return a value when it is not undefined. This complexity may potentially introduce inconsistencies between the two Choose statements. Heck, it is confusing just writing about it.

Continuing our example where the Colors parameter contains the above Choose statement, we could simplify the above IIf statement as follows:

=IIf(
      IsNothing(Parameters!Colors.Value),
      “Black”,
      Parameters!Colors.Value
)

Now we have a very simple IIf statement. By abstracting the Choose statement into a parameter we are able to reuse the single evaluation of the Choose for both the IIf’s Boolean expression as well as its “return if true” value. Since there is now only 1 Choose statement, it cannot fall out of synch. In this case the complexity results from the distributed approach of the solution. The Colors parameter contains the Choose statement and the Choose statement is controlled by the Palette parameter. The Colors parameter is consumed in the IIf statement.

Another work-around is to accommodate an undefined return value wherever the results of the Choose are consumed. In other words, don’t try to return a value for every possible condition, instead accept that null is one of the possible return values. This makes the Choose expression easier to read and consume but multiplies the number of IIf statements, one for each place the Choose statement results are used. The result is readable expressions, but more of them.

The last solution I will propose is to use Choose only when you can ensure your Integer condition can be limited to a known, sequential set of positive Integers beginning at 1. There are more details on how to achieve this in the section on The Integer Decision Engine below.

The Integer Decision Engine

The Choose statement’s integer decision engine has some very unique characteristics that can make for a cleaner expression than Switch or (multiple) IIf statements, especially with larger sets of data (10, 20, etc.). But there are some limitations that may make it impractical to use in some cases. When the Choose Integer expression, or key, does not begin with 1 and/or the expected values are not sequential, the statement syntax can become unmanageable.

Since the Choose statement expects the lowest value of the key clause to be 1, if the key (Fields!Key.Value) begins at a number greater than 1, say 1000, you should adjust it so that the result begins at 1. Given this scenario, I would set the Choose key clause to “Fields!Key.Value-999”. Also, if the resulting values are not sequential, they should be adjusted to be sequential, or as close to sequential as possible. Given the previous scenario where the lowest value of the key is 1000, if my subsequent values are in increments of 100, I would set the key clause to (Fields!Key.Value/100)-9. An aptitude for math can useful when working with Choose.

When your raw data cannot be easily manipulated to produce a 1-based set of sequential, positive integers, you may be able to create a calculated field to meet your needs. There are various ways of creating calculated fields, both in your dataset and in the report.

In a Dataset

A calculated field can be added directly in the dataset query or it can be calculated using the SSRS expression builder in the dataset Fields tab by selecting “Add”, then “Calculated Field”. Now click the “fx” button next to the newly added field. This opens expression builder in the scope of the dataset allowing you to create an expression using parameters, constants, custom code modules, or other fields returned in the query.

The methods for implementing calculated fields in your dataset query will vary depending on the type of dataset. For a dataset using the Transact-SQL query language, we could use CASE WHEN to validate a set of conditions and return an integer beginning at 1.

SELECT
   CASE     WHEN UPPER(Column1) LIKE ‘%BLUE%’ THEN 1
                WHEN UPPER(Column2) LIKE ‘%BERRY%’ THEN 1
                WHEN UPPER(Column1) = ‘NONE’ THEN 2
                WHEN UPPER(Column3) LIKE ‘START%’ THEN 3
                ELSE 4 END AS SeqID

Transact-SQL also includes the ROW_NUMBER function which will return a sequential set of positive integers beginning with 1 and incremented by 1:

SELECT
    Column_1,
    Column_2,
    …,
    ROW_NUMBER() OVER (PARTITION BY [Partition Expression] ORDER BY [Order by clause]) AS SeqID

There are, of course other ways of creating an appropriate key for a Choose statement in your dataset. The rest are up to you to discover and create.

In the Report

If you cannot change the underlying dataset, SSRS expression builder contains a library of expressions that can be used individually or in combination to create an appropriate key, including a RowNumber expression. It is a little more limited than the Transact-SQL Row_Number statement but it is possible to control the results similarly to its Transact-SQL cousin. RowNumber also returns a positive sequential integer, beginning with 1 for the first record in the specified scope. RowNumber, like many aggregate expressions, includes a scope parameter that can be set to any valid group name at or above the current scope. This works similar to the [Partition Expression] used in the Transact-SQL Row_Number expression. The Sorting tab in the containing tablix or group properties can be used to control the order in which the RowNumber is assigned.

Since Choose is itself an expression, a calculated value using expression builder can be placed directly in the Choose key clause wherever Choose is used or it can be placed in another vehicle like a tablix cell or a report parameter. If your expression is placed in a tablix cell, it can be referenced using the ReportItems collection. Both the ReportItems collection and the Parameters collection are available at the report level however a specific ReportItem may be unavailable depending on its specific scope. This may limit which SSRS expressions can be used in creating your solution.

The ReportItems collection is a way to access information contained within the controls of your report. The syntax is similar to the syntax for a field or parameter: ReportItems!ControlName.Value. If I add my calculation in TextBox46 in a table cell in my report, I can get that value using ReportItems!TextBox46.Value.

Extending Strengths

As a report writer, I am always looking for ways of extending the strengths of my tools as well as overcoming or working around the weaknesses. SSRS provides some very powerful ways of doing this with custom code or just using SSRS expressions in combination to complement one another. This opens a world of possibilities that cannot be fairly addressed in this article or, for that matter, in any single article.

See Also