Problem
Just like in any other programming language there is a need for conditional logic. In this article we explore how to use conditional logic in SSRS using the SSRS iif, SSRS switch and SSRS choose 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) SSRS IIF, 2) SSRS SWITCH and 3) SSRS CHOOSE.
Most developers are somewhat familiar with the iif method, but switch is less common and choose even lesser known. Choose is a SQL Construct in SELECT statements and the focus in this tip will be 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.
SSRS Installation Steps
The SSRS install and configuration process does require SQL Server, but it does not have to be on the same server as the database. The SSRS process uses a standalone installer as 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 tips 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 installed, 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. Example usage includes:
- Textboxes
- Formatting including
- Background color
- Font color, size, and weight
- Borders
- Field values
- Show and Hide options and toggle
- Groups, row and column
Using the SSRS IIF Function
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.
Add New Fields
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. Click the Fields tab on the Dataset Properties window. Click Add, to add a new field at the bottom of the list. We will select the f(x) button to set the expression.
Setup Expression
The next step is creating a simple expression that compares the order year to maximum order year. The “Max Year” will be returned, if the year matches. Otherwise, the expression will return “Prior Year”.
The new flag field is added as a new column group.
Review Report
Running the report now shows the breakout of the year based on the max year flag expression.
SSRS IIF Function to Change Font Color Based on Total Due Sum
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.
The current font is black, for the Total Due text box.
Next, clicking on the down arrow on the right side and then selecting Expression opens the Expression Builder windows.
Three iif statements are needed for our logic. Each is nested inside the prior iif statement.
The results turnout as expected.
The nesting of iif statements, for a large number of values, gets 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 the SSRS SWITCH Function
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 the SSRS CHOOSE Function
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 SSRS 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 SSRS 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 SSRS 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
