SSRS IIF condition

This SSRS article shows how to use the IIF condition or expression to change the background colors or create a new column based on the existing one with custom messages.

Right-Click on the Datasets folder to create a new DataSet. The below screenshot shows the data set that we use for this SSRS IIF condition example.


The Sql query that we used above SSRS example is:

SELECT Prod.EnglishProductName AS ProductName, 
       SUM(Fact.OrderQuantity) AS Orders, 
       SUM(Fact.TotalProductCost) AS ProductCost, 
       SUM(Fact.SalesAmount) AS Sales, 
       SUM(Fact.TaxAmt) AS Tax
FROM FactInternetSales AS Fact
   INNER JOIN DimProduct AS Prod ON Fact.ProductKey = Prod.ProductKey
   GROUP BY Prod.EnglishProductName, Prod.Color

We have designed a simple table report of Product sales and formatted the font and colors.

Table Report

Let me show you the report preview.

Report Preview

SSRS IIF condition Example

In this example, we will change the background color of the orders column based on the IIF condition or expression. So, select the Orders column, go to the properties to change the background color, and click the Expression hyperlink.

Go to Column Properties

The below IIF statement will assign Light Green to orders above 400, and for the remaining rows, it will assign an Orange color.

=IIf(Fields!Orders.Value > 400, "LightGreen","Orange")
SSRS IIF conditions change the background color of a column

If you observe the report review, there are two different background colors for the order column. 

SSRS IIF condition report preview

SSRS Nested IIF Statement

Let me create a new column on the right side of the Tax and rename it as Message. Next, right-click the empty space and choose the Expression option.

Create a New Column to write expression

Here, we used the Nested IIF condition to display a message based on product sales.

  • For above 100000, it’s Good.
  • For above 50000, it’s Average.
  • And for all the remaining rows, Bad.
=IIf(Fields!Sales.Value > 100000, "Good", 
IIf(Fields!Sales.Value > 50000, "Average", "Bad"))
SSRS Nested IIF condition to write a message

You can see the Expression in the final report design.

Final Report

If you observe the SSRS IIF condition report review, there are Good, Average, and Bad messages.

SSRS IIF conditionReport Preview