SQL Reporting Services Alternating and Varying Row Styles

• Download source code - 597 KB

Introduction

Often we require our data rows / lines to have different colors for various purposes and reasons. The colors may be different purely for improving readability or based on data. While ASP.NET components offer very powerful tools to achieve that, SQL Reporting has only limited functionality. However, even with it, one can get fancy.

For the purpose of simplicity, I will not get fancy, but only demonstrate how to have your row styles changed in batches of 4. I.e., row colors will repeat not each second row, but each fourth. From the solution, you will see that the number doesn't really matter.

Also, rows will change color depending on data. See that the rows with "Owner" in Contact title are yellowish, and other colors repeat each fourth row:

Using the project

Just download the project, unzip and open it in Visual Studio 2005. You must have SQL Server components installed though. The rest is straightforward.

Data

I am using a simple Northwind MS Access *.mdb database. The report is connected to it through an OLEDB MS Jet engine and the name of the data source is: DataSource1.

What we color

1. Each fourth row will be repeating in color.
• Row 1: Color 1
• Row 2: Color 2
• Row 3: Color 3
• Row 4: Color 4
• Row 5: Color 1
• Row 6: Color 2

and so on...

2. Rows where the field "Contact Title" value is "Owner" is painted "PaleGoldenrod".

How we color

It's so simple that I don't believe I am putting an article. I guess, the fact that I couldn't find the solution for some time makes me do this.

In order to set the color of the row, we first select the row in the Design Layout mode:

Open its properties. For BackgroundColor, instead of a color, we select <Expression...>:

A new window pops up:

where we build our expression which will determine the background color of each row. Keep in mind that we are dealing with VBA here, thus your expression has to be a "one-liner" as in MS Excel, for example.

The code

In order to satisfy our requirement, we put the following code into the BackgroundColor expression:

= IIF(Fields!ContactTitle.Value = "Owner", 
  "PaleGoldenrod",   IIF(RowNumber("DataSource1") Mod 4 = 1, 
  "White",  IIF(RowNumber("DataSource1") Mod 4 = 2, 
  "#f0ffff", IIF(RowNumber("DataSource1") Mod 4 = 3, 
  "#e0eeee", "#c1cdcd"))))

The code does the following:

If the Contact Title value is "Owner", then it paints the field in "PaleGoldenrod", else it checks for the field's relation to the number of fields to skip before repeating color.

The best way to do so is through using mod. While Div (which is represented by DivRem in this version of VBA) might have done the trick, it can't be used for because it requires a variable to receive the remainder, which we don't have and don't need.

Styles

While I only deal with BackgroundColor here, you can change other style properties based on the expression. For example, we could have had rows backgrounds colored only for readability and highlight "Owner" by changing font properties based on data.

Simpler

If you simply want rows to alternate in color, please refer to: http://www.codeproject.com/KB/database/SQLReports.aspx.