Changing the background color of rows can be a valuable method of visually grouping data in a Reporting Services (SSRS) report, as demonstrated in the screen shot below.

Dynamically changing the background color requires custom code that can compare the current value of a field with its value in the previous record and cell expressions that toggle the cell background color if the two values are not the same.

Overview

In this article we will:

1. Create and populate a table containing stock market data
2. Create a single-parameter query that selects data from the table
3. Create an SSRS report based upon the parameterized query
4. Add custom code to the report that defines two functions that set the background color of cells
5. Add expressions that reference the two custom code functions to cells in the report
6. Execute the report to demonstrate that the custom code works

1. Create and populate the source table

Download the resource file 'sp500hst.zip' to the folder 'C:\testfiles\' and extract its contents.

Create and select a database named 'TestDB' if it does not already exist and execute the following SQL code...

BEGIN TRY
    DROP TABLE [TestDB].[dbo].
END TRY
BEGIN CATCH
END CATCH
CREATE TABLE [TestDB].[dbo].
    ([Date] VARCHAR(8)
    ,[Ticker] VARCHAR(5)
    ,[Open] DECIMAL(16,2)
    ,[High] DECIMAL(16,2)
    ,[Low] DECIMAL(16,2)
    ,[Close] DECIMAL(16,2)
    ,[Volume] INTEGER)
BULK INSERT [TestDB].[dbo].
   FROM 'C:\testfiles\sp500hst.txt'
   WITH
      (
         FIELDTERMINATOR =',',
         ROWTERMINATOR = '\n',
         FIRSTROW = 2
      )
SELECT * FROM [TestDB].[dbo].

...in a 'SQL Server Management Studio' query window...

...to create and populate the 'SP500' table.

2. Create a single-parameter query

Paste the following SQL code...

DECLARE @Volume AS INTEGER
SET @Volume = 1000000
SELECT [Date]
      ,[Ticker]
      ,[Open]
      ,[High]
      ,[Low]
      ,[Close]
      ,[Volume]
FROM [TestDB].[dbo].
WHERE Volume > @Volume
ORDER BY Ticker

...into a 'SQL Server Management Studio' query window and execute it...

...to select records with a volume greater than 1,000,000.

3. Create an SSRS report

Create or open an SSRS report project in Visual Studio, right-click on the 'Reports' folder and select 'Add New Report' - 'New Item" from the popup menu.

Change the 'Name' textbox to 'SP500.rdl' in the 'Add New Item' dialog window, then double-click the 'Report Wizard' template.

Configure the data source to the server containing the 'TestDB' database and click the 'Next' button.

Paste the query we created earlier (minus the parameter declarations) into the 'Query string' text box and click the 'Next' button.

Accept the 'Tabular' default report type by clicking the 'Next' button.

Highlight all seven available fields and click the 'Details' button...

...to add all seven fields to the detail section of the report, then click the 'Finish >>|' button.

Click the final 'Finish' button...

...to create the report.

Click the 'Preview' tab, enter 1000000 into the 'Volume' parameter field, then click the 'View Report' button...

...to display the stock market records with volume greater than one million shares.

It's not visually obvious where records for one 'Ticker' symbol ends and another begins.

4. Add custom code

Click the 'Design' tab, then select 'Report' - 'Report Properties' from the top menu.

Select the 'Code' tab of the 'Report Properties' window...

...and enter the following VB code...

Public Dim Color As String = "White"
Public Function ToggleColor(ByVal PreviousTriggerColumnValue As String, ByVal CurrentTriggerColumnValue As String ) As String
If PreviousTriggerColumnValue <> CurrentTriggerColumnValue Then
   If Color = "White" then
      Color = "WhiteSmoke"
   Else
      Color = "White"
   End If
End If
Return Color
End Function
Public Function GetColor() As String
Return Color
End Function

...into the 'Custom code' text box.

Click the 'OK' button to close the 'Report Properties' window.

5. Add expressions to report cells

Right-click the 'Date' data cell and find the 'Background Color' property in the 'Properties' box on the right side of the window. Click on the drop-down button and click on the Expression' item.

Paste the following expression...

=Code.ToggleColor(Previous(Fields!Ticker.Value), Fields!Ticker.Value)

...into the 'Expression' text box and click the 'OK' button to close the 'Expression' window.

Right-click the 'Ticker' data cell and select the 'Expression' menu item.

Paste the following expression...

=Code.GetColor()

...into the 'Expression' text box and click the 'OK' button to close the 'Expression' window.

Add the same background color expression to the remaining five fields to complete the report configuration.

6. Execute the report

After adding the expression '=Code.GetColor()' to the remaining fields, click the 'Preview' tab, enter '1000000' into the 'Volume' text box, then click the 'View Report' button.

Conclusion

This article has demonstrated how to dynamically toggle the background color of an SSRS report using custom code and expressions.