Add alternating row color to SQL Server Reporting services report

Question

How do you shade alternating rows in a SQL Server Reporting Services report?

---

Edit: There are a bunch of good answers listed below--from quick and simple to complex and comprehensive. Alas, I can choose only one...

Answer 1

Go to the table row's BackgroundColor property and choose "Expression..."

Use this expression:

= IIf(RowNumber(Nothing) Mod 2 = 0, "Silver", "Transparent")

This trick can be applied to many areas of the report.

And in .NET 3.5+ You could use:

= If(RowNumber(Nothing) Mod 2 = 0, "Silver", "Transparent")

Not looking for rep--I just researched this question myself and thought I'd share.

Answer 2

@mharen, Good post. I've had to do this myself, and have to look it up every time b/c I forget the syntax!!

Answer 3

Using IIF(RowNumber...) can lead to some issues when rows are being grouped and another alternative is to use a simple VBScript function to determine the color.

It's a little more effort but when the basic solution does not suffice, it's a nice alternative.

Basically, you add code to the Report as follows...

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

Then on each cell, set the BackgroundColor as follows:

=Code.AlternateColor("AliceBlue", "White", True)

Full details are on this Wrox article

Answer 4

for group headers/footers:

=iif(RunningValue(group on field,CountDistinct,"parent group name") Mod 2,"White","AliceBlue")

Answer 5

You will get a 'chess' efect when You'll use it on matrix

Answer 6

chess effect requires an odd number of columns

Answer 7

One thing I noticed is that neither of the top two methods have any notion of what color the first row should be in a group; the group will just start with the opposite color from the last line of the previous group. I wanted my groups to always start with the same color...the first row of each group should always be white, and the next row colored.

The basic concept was to reset the toggle when each group starts, so I added a bit of code:

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
'
Function RestartColor(ByVal OddColor As String) As String
 bOddRow = True
 Return OddColor
End Function

So I have three different kinds of cell backgrounds now:

1. First column of data row has =Code.AlternateColor("AliceBlue", "White", True) (This is the same as the previous answer.)
2. Remaining columns of data row have =Code.AlternateColor("AliceBlue", "White", False) (This, also, is the same as the previous answer.)
3. First column of grouping row has =Code.RestartColor("AliceBlue") (This is new.)
4. Remaining columns of grouping row have =Code.AlternateColor("AliceBlue", "White", False) (This was used before, but no mention of it for grouping row.)

This works for me. If you want the grouping row to be non-colored, or a different color, it should be fairly obvious from this how to change it around.

Please feel free to add comments about what could be done to improve this code: I'm brand new to both SSRS and VB, so I strongly suspect that there's plenty of room for improvement, but the basic idea seems sound (and it was useful for me) so I wanted to throw it out here.

Answer 8

My problem was that I wanted all the columns in a row to have the same background. I grouped both by row and by column, and with the top two solutions here I got all the rows in column 1 with a colored background, all the rows in column 2 with a white background, all the rows in column 3 with a colored background, and so on. It's as if RowNumber and bOddRow (of Catch22's solution) pay attention to my column group instead of ignoring that and only alternating with a new row.

What I wanted is for all the columns in row 1 to have a white background, then all the columns in row 2 to have a colored background, then all the columns in row 3 to have a white background, and so on. I got this effect by using the selected answer but instead of passing Nothing to RowNumber, I passed the name of my column group, e.g.

=IIf(RowNumber("MyColumnGroupName") Mod 2 = 0, "AliceBlue", "Transparent")

Thought this might be useful to someone else.

Answer 9

If for the entire report you need an alternating color, you can use the DataSet your Tablix is bound to for a reportwide indentity rownumber on the report and use that in the RowNumber function... =IIf(RowNumber("DataSet1") Mod 2 = 1, "White","Blue")