Expression for SSRS Switch Statement

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 screenshot shows the data set that we use for this SSRS Switch Statement example.

DataSet

The Sql query that we used above 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.

Table Report

Let me show you the report preview.

Report Preview

SSRS Switch Statement Example

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 details cell and choose the Expression option.

Add Column to Write a custom expression

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