Zebra Striping in SQL Reporting Services

Zebra striping, also known as alternating row colors or candy striping, is the application of a different color or shading to alternating rows in a data table.  The striping not only makes your data more aesthetically pleasing, but it can also assist guide the reader's eye along the row.  This effect may be even more beneficial for large tables of similar data or tables with large amounts of whitespace separating the columns where the eye may have a tendency to wander into adjacent rows.

In most languages, it would be pretty trivial to add this, such as the method below, which takes advantage of the handy modulo + ternay combo to accomplish the task.

for(i = 0; i < rows.Count(); i++) {
    i%2 == 0 ? 'even' : 'odd';
}

But how do we apply this in reporting services?

Reporting Services

This is actually pretty simple and there are a couple of ways to get this done.  The easiest method would be to use an expression for the BackgroundColor property for the row.

  1. Select the row(s) you wish to apply the zebra striping.
  2. In the properties window, select the dropdown next to BackgroundColor and choose "<Expression>", this will open the expression editor.
  3. Insert the following expression:
    =IIF(RowNumber(Nothing) Mod 2, '#E7E7E7', '#FFFFFF')

Note that the RowNumber function takes an argument for the scope, check the function reference for more details.

This should give you a result that resembles the following:

Striped table

However, you may notice that this can go a little haywire when applied to a grouped dataset.  You may wind up with a situation like below where there are multiple rows of the same colors bunched instead of alternating as expected.

Grouped striping problem

Grouping, Zebra Striping, SSRS and You

If you end up in a situation where the RowNumber isn't working for you quite as expected, a small function can come to the rescue.

Private Alt As Boolean Function Stripe(ByVal NewRow As Boolean, ByVal OddColor as String, ByVal EvenColor as String) As String
   If NewRow Then Alt = Not Alt ' Trip the switch denoting a new row
      If Alt Then
         Return OddColor
      Else
         Return EvenColor
      End If
   End If
 End Function

To use this function, in the main menu choose Report > Report Properties and add it to the Code tab.  Now, instead of setting the background color for the entire row, we'll make a call to this function on the first and the remaining columns.

  1. Select the first column of the row you wish to apply the striping to.
  2. In the properties window, select "<Expression>" for the BackgroundColor dropdown.
  3. In the expression editor use the following expression:
    =Code.Stripe(True, '#E7E7E7', 'White')
  4. Select the remaining columns on the row, select "<Expression>" for the BackgroupColor property.
  5. In the expression editor use the following expression:
    =Code.Stripe(False, '#E7E7E7', 'White')

You'll notice the expressions are nearly identical except for the first argument, the boolean value for "NewRow". Since the NewRow argument is the only thing that changes, your striped row should now be calling the Code.Stripe function with the following values:

Header 1 Header 2 Header 3 Header 4
True False

What this does is tell the stripe function that we're starting a new row and to swap the color. All the other columns in the row are set NewRow to false since they belong to the same row and we don't want the color to change. This small bit of code works for both grouped and un-grouped content. This means you can use this instead of the RowNumber method above, but keep in mind you'll need to update 2 locations instead of just one. So it's probably better to use the RowNumber method to stripe a basic table and use the custom function when needed.

And there you have it, striped rows made easy.