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.
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.
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")
- Guide to Hide Table Nulls and Replace Table Nulls articles that can show real-time examples of IIF expression.
If you observe the report review, there are two different background colors for the order column.
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"))
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.
