Highlight SSRS Table Min and Max Rows

This SSRS article explains how to highlight the minimum (min) and maximum (max) rows in a table report using the IIF condition or Switch statement expressions.

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

The Sql query that we used above SSRS example is:

SELECT [FirstName] +  ' ' + [LastName] AS FullName
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [Employee]

We have designed a simple table report of Employee Sales and formatted the font and colors. Let me show you. the preview of the report.

Highlight SSRS Table Min and Max Rows

Select the complete details row and go to the Background color properties window. Next, click the Expression hyperlink to writing an expression.

Here, we used the Nested IIF condition to check whether the Sales value equals DataSet Maximum. If True, assign Sea Green Color to that row. Otherwise, the second iif condition checks whether the Sales value equals DataSet Minimum. If True, Orange; else, Transparent color.

=IIf(Fields!Sales.Value = Max(Fields!Sales.Value, "EmpDataSet"), "SeaGreen", 
IIf(Fields!Sales.Value = Min(Fields!Sales.Value, "EmpDataSet"), 
"Orange","Transparent"))

Expression to Highlight SSRS Table Min and Max Value Rows

Now, if you see the table report preview, the Minimum and maximum rows are highlighted.

Highlight SSRS Table Min and Max Rows Preview

Let me replace the IIf condition with the below switch expression. It changes the background color of Minimum, Maximum, and sales greater than average sales highlights table rows.

=Switch(Fields!Sales.Value = Max(Fields!Sales.Value, "EmpDataSet"), "SeaGreen", 
	Fields!Sales.Value = Min(Fields!Sales.Value, "EmpDataSet"), "Orange",
	Fields!Sales.Value >= Avg(Fields!Sales.Value, "EmpDataSet"), "CornflowerBlue",
	Fields!Sales.Value < Avg(Fields!Sales.Value, "EmpDataSet"),"Transparent")

Preview the report.