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

Weblog by Paul Turley and SQL Server BI Blog.