Alternate Row Colors in an SSRS 2000 or 2005 Matrix

To create a “greenbar” report in a table with alternating row colors, you can simply use the RowNumber function to determine whether a row is odd or even. This also works with the newer Tablix-style matrix in SSRS 2008.  However the matrix data region in SSRS 2000 and 2005 doesn’t support the RowNumber function so it requires a different technique.  This can be accomplished using a simple embedded custom code function in the report.

Open the Report Properties dialog and create a Visual Basic.NET function in the Code page by entering the following code:

        Private bOddRow As Boolean

Function AlternateColor (ByVal OddColor As String, ByVal EvenColor As String, _
                          ByVal Toggle As Boolean) As String
    If Toggle Then bOddRow = Not bOddRow
    If bOddRow Then
        Return OddColor
    Else
        Return EvenColor
    End If
End Function

This function takes three arguments; the name or numeric string for the odd color rows, the name or numeric string for the even color rows and a Boolean (e.g. True or False) flag used to switch between odd and even row modes. This function is called in an expression for the BackgroundColor property for every detail cell in the matrix and at least one row header cell.

For the group value that will be used to alternate row colors, edit that row group cell textbox properties and use the following expression for the BackgroundColor property.

=Code.AlternateColor(“AliceBlue”, “White”, True)

Of course, you can use whatever colors you want for the odd and even background colors. These can either be standard web color names, like those you see in the SSRS color property list, or a six character hexadecimal color value proceeded by a pound sign.

For each cell textbox in the data region of the Matrix, enter the same expression for the BackgroundColor property, only change the third argument to False:

=Code.AlternateColor(“AliceBlue”, “White”, True)

That’s it. When you run the report, the background color will change every time the group value changes.

How does it work? The matrix is rendered like a typewriter (for those of you born after the dawn of the Information Age, that’s a device we used to use to fill out forms and send text messages.) The cells are rendered from top to bottom and from left to right; returning to the left-most cell on the next row after it?s done with the row above. When the renderer encounters a new group value, it creates a new instance of the row group cell which toggles the private bOdd Boolean variable. On the first row, it gets flipped from False be True (we?re on row #1, which is an odd number.) Each cell on that row gets set with the odd row color. This continues until there?s a new group value, which flips bOdd to False (row #2 is an even color) and so on.

Detailed step-by-step instructions for this technique may be found in the Wrox Press book: Professional SQL Server 2005 Reporting Services, Chapter 7, pages 272-277.

SQL Server BI Blog | Reporting Services Design