Report Solution Patterns and Recipes - Professional SQL Server 2005 Reporting Services - page 273

The Challenge:

❑ Reporting Services does not have a built-in alternating row color feature.

What you'll need:

❑ A Visual Basic function.

❑ Expressions used to call the function on the BackgroundColor property of row items.

I've seen a few different techniques used to implement this feature and they all require complex expres-
sions or some use of Visual Basic programming. Fortunately, this isn't hard to do, even if you're new to
VB programming. This technique involves using a VB function to return a different color for odd and
even rows. Report items are rendered from top to bottom and then from left to right — as the carriage of
a typewriter does (for the younger generation, a typewriter is sort of like a computer with moving parts).
This means that custom code procedures and expressions associated with report item properties will
always be executed in this order. In our solution, the start of a new row is indicated by passing a toggle
flag when the function is called in the leftmost column's text box. The following Visual Basic code begins
with a class module-level variable used to hold the odd-or-even row indicator between calls. As you can
see, the bOddRow variable value is toggled between True and False on each call.

Private bOddRow As Boolean

'*************************************************************************
' -- Display green-bar type color banding in detail rows
' -- Call from BackGroundColor property of all detail row textboxes
' -- Set Toggle True for first item, False for others.
'*************************************************************************
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

The program code is entered on the Code tab of the Report Properties dialog. To access this window,
choose Report Properties from the Report menu while using the report designer's Layout tab. This is
shown in Figure 7-1. After entering or making modifications to code, click the OK button to update the
report definition.

For the BackgroundColor property of the first text box (in the leftmost column of the row), enter the
following expression to call the custom code function:

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

The first parameter is the name of the background color for odd-numbered rows. The second is the back-
ground color for even-numbered rows. These two values may be the name of any standard web color
(available from the color drop-down list in the designer). These may also be any one of about 16 million
Pantone colors expressed in web-style hexadecimal format (for example, #FF8000 for orange and
#9932CD for dark orchid).