Case statement is one of a widely used program flow statements. It allows us to control the program flow and it’s outputs when we have more than two flows/conditions to set. In essence, case statement is an optimized version of nested If statement.
In SSRS, case statement is a decision function that can be reached through expressions.
Important thing to mention is that in SSRS, a keyword for case statement is Switch, and not case like in other similar technologies.
The syntax of the case statement in SSRS is the following:
Switch(Expression as Object)
This function will act in the same way as IIf function. An Expression object consists of two parts, where the first part is the one to be evaluated, and the second part is the result of the condition to be met.
In the example above, the Switch function will return I in case we apply it on student which has id=1. In case student id is equal to 2, the function will return II.
In order to explain how to use SSRS case statement, we will use the data provided in the table below and add additional column called Department Abbreviation which will be populated by using Switch function.
Example of basic Switch function usage
We have created a report which displays the data from Students table.
Figure 1: Basic report structure
Now we want to add column named Department Abbreviation. In this step, we want to demonstrate basic usage of Switch function, so we will populate the column in the following way:
- If Department ID equals to 1, column will be populated with CS
- If Department ID equals to 2, column will be populated with EN
- If Department ID equals to 3, column will be populated with AU
- If Department ID equals to 4, column will be populated with TK
Steps of creating and populating Department Abbreviation column is the following:
1. Add a column to a table item
Figure 2: Adding new column to a table item
2. Define the header name of the column
Figure 3: Adding column header name
3. Define the expression as a column value
Figure 4: Adding the expression as a column value
4. Under Category, choose Common Functions -> Program Flow. Under Item, choose Switch.
Figure 5: Setting Switch function
5. Set Expression value as provided in the picture below
Figure 6: Defining Switch function
Here, we evaluate the value of Department ID column, and based on that value we populate new column with different values.
After we define Switch function as presented in the previous steps, we click OK and make a preview of the report. The result is the following:
Figure 7: A column populated based on Switch function
Example of nested Switch function
Switch statement can be nested, and this example will demonstrate this situation.
In this example, we want to create Student Group column which will be populated based on values in two columns:
- If a student is younger than 25 and belong to department 1, a Student Group column will be populated with CS BACHELOR
- If a student is older than 25 and belong to department 1, a Student Group column will be populated with CS MASTER
This logic is applied to students in other departments as well, using abbreviations from the previous example (EN for department 2, AU for department 3, TK for department 4).
After we have added the Student Group column in report table item, we define the expression which populate Student Group column int the following way:
Figure 8: An example of a nested Switch function
In the previous picture we can see how we have designed nested Switch function, in order to get the desired result: we start with checking out if department ID is 1: if it is, we check student’s age: if the student is younger than 25, we populate the column with CS BACHELOR value, otherwise we populate the column with CS MASTER value. The same logic is used for all other students related to other departments.
The result of such nested Switch function we can see in the following picture by checking out the Student Group column:
Figure 9: A column populated based on nested Switch function
Instant livechat to an Expert!
Most of the time, the problem you will need to solve will be more complex than a simple method. If you want to save hours of research and frustration, try our live SQLExpert service!
Our SQLQuerychat Experts are available 24/7 to answer any questions you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.