SSRS Switch Statement

This SSRS article shows how to use the Switch case or statement inside an expression to change the background colors or create a new column 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 Switch Statement 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

SSRS Switch Statement Example

We have designed a simple table report of Product sales and formatted the font and colors. In this example, we will change the orders column background color based on the Switch case or expression. So, select the Orders column and go to the properties. Next, click the Expression hyperlink to change the background color based on the switch condition.

Background Color Expression

The below SSRS Switch statement will assign five colors to the Orders column based on the order value.

  • If Orders are less than 50, Pink.
  • Orders less than or equal to 100, Tan. 
  • Orders less than or equal to 200, Gold. 
  • Orders less than or equal to 1000, Pale Turquoise. 
  • Orders greater than 1000, Light Green. 
=Switch(Fields!Orders.Value < 50, "Pink",
		Fields!Orders.Value <= 100, "Tan",
		Fields!Orders.Value <= 200, "Gold",
		Fields!Orders.Value <= 1000, "PaleTurquoise",
		Fields!Orders.Value > 1000, "LightGreen")
Expression for SSRS Switch Statement

If you observe the order column in the report review, there are five different background colors.

Add Background Colors using SSRS Switch Statement Preview

Custom Column Example

Let me create a new column on the right side of the Sales and rename it as Message. Next, right-click the empty details cell and choose the Expression option from the context menu. For more functions >> Click Here!

Here, we used the SSRS Switch statement or case to write custom information to the message column based on the Sales value.

=Switch(Fields!Sales.Value <= 10000, "Bad",
		Fields!Sales.Value <= 25000, "OK",
		Fields!Sales.Value <= 75000, "Average",
		Fields!Sales.Value <= 100000, "Breakeven",
		Fields!Sales.Value <= 400000, "Good",
		Fields!Sales.Value > 400000, "Profit"
		)
SSRS Switch Statement Case Expression

If you observe the report review, there are Good, Breakeven, OK, Average, Profit, and Bad messages.

SSRS Switch Statement or case Preview