Adding alternating row colors in SQL Server Reporting Services (SSRS) is generally easy to do if it is needed for every row of a table. If there is no row groups defined in the report, and you need to create alternating row colors for groups of data, you can use code in SSRS to handle the grouping.
The first thing to do is create a new report in SSRS Report Builder. Visual Studio Tools will work too. I will be using customer information in Dynamics GP as my data set to get a list of customers and their locations.
We want to order the customer SQL query how it will be grouped. In this example, I am grouping by state, then city, and we will highlight states with our alternating rows.
The query I am using:
Next, we will add a table for the data using the wizard:
Running the report produces this:
To add our row grouping using VBA, we need a couple things in our report:
- Two report variables that allows the report engine to understand which row is being evaluated and whether the row needs to be highlighted a color or not
- Custom report function code to work with the report variables
- An expression on the row's background color which uses the report function
To add our report variables, we need to access the report properties window (right-click the non-report area, and choose "Report Properties...")
In the custom code window, I will type in the following code (VBA):
Here is what this code is doing:
- Three variables are passed into the function: val, seq, and evalString
- The first two variables are references to report level variables. This is why there is a reference to the the reporting object model.
- The last variable is a string to the current row in the table that we just created. It will be used to evaluate against the report variable
- If the report value is not the same as the current row value
- Set the system value to the new one
- Update the sequence number by adding 1
- This will return the sequence value
Next, we add the report level variables in the same Report Properties window in the Variables section.
The variables can be named what you like. They will be referenced in the expression I will create later. The first variable will hold the string value to be evaluated. The next variable will hold the sequence number that counts by one for each group.
Note: I have unchecked the Read-Only check boxes. This will allow me to assign values to these variables in my code.
Lastly, we need to add the expression which will assign the colors to the alternating rows.
Highlight the row tied to the data source in the table created, and in the properties window, locate the Background Color property. Select Expression from the drop down.
In the Expression window, I am adding the following expression with no line breaks:
This expression says, if my code returns a number that has a remainder of 0 when I divide it by 2, make the row color green. If not make it white.
Note that I am passing in the variables (not the variable values) for the first two parameters of my function. Next I am passing in the field that I want to be evaluated (Fields!State.Value). This can be any field in your data set. If not using a string, make sure to convert it to a string first.
When finished, click OK, and run your report, and your finished product should look like the following:
