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.

If statement in SSRS – Querychat

If statement is a widely used program flow statement. It allows a developer to control the program flow and it’s outputs. In SSRS, If statement is a decision function that can be reached through expressions.

Important thing to mention is that in SSRS, a keyword for If statement is IIf, and not If like in other similar technologies.

The syntax of the if statement in SSRS is the following:

IIf(Expression as Boolean, TruePart as Object, FalsePart as Object)

This statement will return TruePart or FalsePart value, depending on the result of the Expression: if the result of the Expression is true, TruePart will be returned, otherwise FalsePart will be returned.

Example:

IIf(Fields!studentid.Value="1","I","X")

Result:

In the example above,  the IIf function will return I in case we apply it on student which had id=1. In all other cases, the IIf function will return X.

In order to explain how to use SSRS If statement, we will  use the data provided in the table below and add additional column called Department Abbreviation which will be populated by using IIf function.

Table: Students

Example of basic IIf 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 IIf 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 is not 1, column will be populated with XX

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. Assign 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 IIf.

Figure 5: Setting IIf function

5. For expression, choose departmentid column field to be equal to 1, for the TruePart object write down “CS”, and for the FalsePart object write down “XX”.

Figure 6: Defining IIf function

After we have defined if statement 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 IIf function

Example of advanced IIf function usage

If statement can be nested, and this example will demonstrate this situation.

Nested IIf function will be used in populating the Department Abbreviation 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

Since we have already created the column and used IIf function against it in the previous example, in this example we will modify the IIf function we already have.

The modification of the IIf function is reflected in the following:

Figure 8: An example of a nested IIf function

In the previous picture we can see how we have designed nested IIf function, in order to get the desired result: we started with checking out if Department ID is 1: if it is 1, we will populate the Department Abbreviation column with CS value. If it is not 1, we check if Department ID is 2: if it is, we populate the column with EN value. If it is not 2, we check if Department ID is 3: if it is, we populate the column with AU value. If it is not 3 either, we populate the column with TK value.

The result of such nested IIf function we can see in the following picture by checking out on Department Abbreviation column:

Figure 9: A column populated based on nested IIf 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