SSRS IIF, Switch and Choose Functions for Dynamic and Appealing Reports

By: Scott Murray   |   Updated: 2021-09-17   |   Comments (1)   |   Related: > Reporting Services Development

Problem

What are the various logical functions and scenarios that can be used in a SSRS report including items like CASE and IF statements?  What is the syntax for these functions?

Solution

SQL Server Reporting Services (SSRS) continues its growth trajectory even in the shadow of Power BI Services as an important business intelligence solution for organizations. Designing simple reports is very easy to complete as defined in these tips: SQL Server Reporting Services SSRS Installation and Configuration Setup and SQL Server Reporting Services Best Practices for Report Design.

However, once a report design dives into SSRS, one dilemma that often surfaces is how to handle basic logical functions that center on problem solution involving case or if type of logical constructs. In particular, this tip will dive into using the functions: 1) IIF, 2) SWITCH and 3) CHOOSE. Most folks are somewhat familiar with the iif method, but switch is less common and choose even lesser known. Surprisingly, choose is actually a SQL Construct that can be used in select statements, but the focus in this tip will be on usage in SSRS. As a report designer is using these functions, the designer should also be cognizant that these functions work hand and hand with the logical functions such as and, or, not, andalso, and orelse.

Getting Started with Installing SSRS

To get started with using this function, you must first install SSRS.

The install and configuration process does require SQL Server, but it does not have to be on the same server as the database. In fact, the process uses a standalone installer now which is outlined in this tip: SQL Server Reporting Services Standalone Installation. However, you must have SQL Server license to install the product. To see this process in action, these tip would be a great staring point: SSRS Install, Setup and Configuration and How to Install and Configure SSRS with Amazon RDS SQL Server. The first tip reviews the basic install process and then moves into configuring SSRS for use while the second covers installing SSRS on AWS. Additionally, you will need to install Visual Studio to complete the design of a report; once Visual Studio is install, the next step is to install the Microsoft Reporting Services Projects extension; please see this tip for details on completing that install: Visual Studio 2019 Install and Configure for the SQL Server DBA.

Logic Choices in SSRS

SSRS provides a whole sundry of different places where the different logic functions can be used to facilitate the selection of a value. Any location that allows the use of an expression can also use these functions to achieve a desired result. Some examples of places where these functions are used utilized include:

• Textboxes
• Formatting including
  • Background color
  • Font color, size, and weight
  • Borders
• Field values
• Show and Hide options and toggle
• Groups, row and column

Using IIF Function in SSRS

Our first use of an iif function will be on a simple report. As shown below the report uses the Adventure Works database and queries the sales table for store sales. The report enables a simple matrix with the Sales Territory Name in the row and the Order Year in the column while the TotalDue is in the data area.

We are going to add a new field to the report data set to determine if the Order Year is the Max or Current Year. As shown below, the dataset properties window is opened, and the Fields tab is selected. After clicking Add, at the bottom of the list a new field is added. We will select the f(x) button to set the expression.

The next step is creating a simple expression that compares the order year to maximum order year. If the year matches, then "Max Year" will be returned. Otherwise, the expression will return "Prior Year".

The new flag field is added as new column group as illustrated next.

Running the report now shows the breakout of the year based on the max year flag expression.

Of course, that is a simple example, but let us move into a more complex example by changing the formatting, specifically, the font color depending on whether the total due sum is less than (<) 1,000,000, between 1,000,000 and 2,500,000, and greater than 2,500,000. The first tier will be red. The next tier will be blue, and the final tier will be green.

In the properties tab for the Total Due text box, the current font is set to black.

Next, clicking on the down arrow on the right side and then selecting Expression opens the Expression Builder windows.

To achieve our desired logic, 3 iif statements are needed and each must be nested inside the prior iif statement, as demonstrated below.

The results turnout as expected.

However, you can clearly see that the nesting of iif statements, especially if you have a large number of values, could quickly get very complicated and difficult to follow. Most folks who work with T-SQL would say, let us just use a Case statement. While that could be used in the dataset T-SQL query, it is not available in SSRS. Nevertheless, SSRS has another similar function called Switch.

Using SWITCH Function in SSRS

Switch works in a similar way to case statements and is more efficient than nested iifs.

As show below, the switch function presents a much cleaner way to represent the desired logic for the font color scheme. Notice each expression has the logic statement and then the desired value, all separated by commas. The switch function allows multiple expressions as used in the 2nd line of the statement that contains "and". Similarly, or, orelse, and andalso could be used in this context.

The 1=1 at the end is the "catch all" if none of the expression fit into the logical values. It represents the final "else", and without the 1=1 expression and value, a blank or null value would result for the font color if none of the conditions were met. It is recommended to always include the catch all in your switch statement.

The switch statement produces the same results as illustrated next. The noted switch statement is really SSRS’s version of conditional formatting; clearly you can expand this formatting to multiple fields and values.

Using CHOOSE Function in SSRS

One additional logic function, that is certainly not used as widely as iif and switch is the choose function. One of the reasons for its limited use centers on the need to tie the choose function in with a parameter value. Ironically SQL also includes a choose function that is also sparsely used in common SQL paths (Logical Functions - CHOOSE (Transact-SQL)). In essence, choose, selects the index value related to the ordinal position selected in a parameter list. An example is probably the easiest way to see an example of its use.

The first step in the process is to create a parameter; in the below example the parameter called Pick_a_Value is created.

Next, the available values are added to the parameter. For this example, TotalDue=1, Tax=2, and Freight=3.

Thus, the value that will get passed to the choose function will be either 1 or 2 or 3. Next, to show the use of the values, two text fields are added. The first, shown below, describes the value being selected in the parameter (TotalDue, Tax, or Freight).

The second added textbox actually displays the sum for the TotalDue, Tax, or Freight values, based on the select value in the parameter, with the index being passed as 1, 2, or 3. As you can see, using this system can quickly allow for the index to drive many values in your report, all without specifying the specific measure, but just referencing the index order.

Summary

In this SSRS tutorial we covered the 3 main logical operators used in SSRS. First, the iif function is likely the most common logical function as it is synonymous with similar functions in many programming languages. However, it does not contain an elseif element, and thus nesting is required if multiple branches and outcomes are required. To address the nested iif functions, SSRS also provides a switch function. The switch function is simpler to write and read as it uses a 1 to 1 setup with the logical statement first and then resulting value second. It allows as many lines as needed and also allows for compound criteria in the logical argument. However, it is recommended that a catchall final logical statement, such as 1=1 is used at the end of the logical test list to prevent a null or blank value being passed. Finally, the choose function can be utilized even though it has a very small usage footprint with few report developers knowing about it or even using it. The choose function provides a mechanism to pass an index integer value to the choose function which will relate to the same position in the list included in the choose function.

Next Steps

• SQL Server Reporting Services (SSRS) Tutorial
• 5 Things You Should Know About SQL Server Reporting Services
• SSRS Install, Setup and Configuration
• SQL Server Reporting Services Unknown but Useful Functions

About the author

Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips