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 code shows the data set that we use for this SSRS IIF condition example.

The Sql query that we used in this SSRS example is:

SELECT Prod.EnglishProductName AS ProductName, 
       Prod.Color, 
       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. 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 SSRS IIF statement or condition 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 (cell) under the Message header and choose the Expression option.

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. If you observe the SSRS Nested IIF condition report review, there are Good, Average, and Bad messages.

SSRS IIF conditionReport Preview