Alternating Rows In SQL Reporting Services

Reporting Services does not support alternating row colors out of the box. But you can get the effect using a little custome code and expressions. Most object properties support expressions. For example, we can specify the background color for a cell as a color (i.e. "White") or a code expression.

To get alternating row colors, I use two functions which I add to the report code module. To access the report code module, view the report options, select the Code tab, and copy the following code into the code window.

Shared AltColorRowCounter as Long

Public Function GetAltColor(color As String, altColor As String, bumpRowCounter As Boolean) As String
  
  If (bumpRowCounter = True) Then
    AltColorRowCounter += 1
  End If

  If (AltColorRowCounter Mod 2 <> 0) Then
      Return color
  Else
      Return altColor
  End If
  
End Function

Public Function ResetAltColor(color As String) As String
  
  AltColorRowCounter = 0
  Return color

End Function

The code window in Reporting Services is painfully small with no code support. I usually do my coding in a script editor and then paste the code into the code window when I'm done. Since Reporting Services accepts most Visual Basic .Net code, I use Visual Studio) to create a .vb file, then do my code editing there. That way I at least get some language intellisense support, formatting and undo functionality to make code editing more pleasant.

The code contains 2 functions and a shared module-level variable. The shared variable AltColorRowCounter is used to hold a row counter, used to determine if we're on an odd or even row. We use this counter to alternate row or column colors. Reports are rendered from top to bottom, left to right. The counter is bumped and reset by our code as the report is rendered, modifying the colors accordingly by calling the following functions.

The ResetAltColor() fuction is used to reset the counter. It returns the string passed in as the color, and I normally use it on a background expression. For example, if I want the alternating rows within a group, and usually like to reset the counter on each group header row. In the group header, I call the reset method with the background color of my group header (in this case LightSteelBlue). To do this, I set the Background expression of the group header. In this example, I set the expression to =Code.ResetAltColor("LightSteelBlue").

On the rows, I call the GetAltColor() function. I pass it the 2 colors I want to alternate, and a third boolean parameter I've named bumpRowCoutner. The bumpRowCounter parameter is used to increment the counter. I normally set it to True on the first column in the row, and leave it False for all additional columns. In this case, I want alternating row colors of White and LightGrey. On the first detail row column, I set the Background expression to =Code.GetAltColor("White", "LightGrey", True). On all the other columns in the row, I set the Background expression to =Code.GetAltColor("White", "LightGrey", False).

This gives me alternating row colors. If I set it to True for each column, I'll get alternating column colors, which can also be a nice effect to have in your bag of tricks. These two functions can be used to give you a wide variety of alternating color effects.