Get instant live expert help with SQL
“My expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Enter your problem description Our Experts are available now

Your message must be at least 40 characters
Select your SQL Environment:
Your message must be at least 40 characters
By submitting this form you agree to Got It's privacy policy.

Case statement in SSRS – Querychat

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.

Example:

Switch(Fields!studentid.Value="1","I",

       Fields!studentid.Value="2","II")

Result:

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.

Table: Students

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.

Leave a Reply

avatar
  Subscribe  
Notify of
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc