How to give group headers or footers alternate colors in a SSRS report

Introduction
I needed to create a SSRS report that only showed group headers (or footers) and still have alternate colors (every other row has another background color).

Problem
Normally I would use an expression like “= iif(RowNumber(Nothing) Mod 2 = 0, “Silver”, “White”)” for the ‘backgroundcolor’ property.

0004-001 AlternateNormalExpression

This would give a nice simple row coloring like:

0004-002 AlternateNormal

(where I have added an expression rownumber(nothing) in the first column to show how it works.

However, when I’m adding a group on the ‘Grp’ field, the rownumber does not work like I would like to use it.

0004-003 AlternateGroupWrong

You can add a scope to the expression, but that only works for making the rownumbers start from 1 on a new group.

=RowNumber(“Grp”)

0004-004 AlternateGroupWithScope

So this still does not give the result that I was looking for. (the first group header should have row 1, the second rownr 2, etc, etc.

Solution
What I ended up doing, is changing the dataset a bit with the use of the DENSE_RANK function so that it would return a incrementing number per group:

SELECT
DENSE_RANK() OVER (ORDER BY Grp) AS GroupNr
,*
FROM tmpAlternate
GROUP BY
Grp
,Name

This gives a promising result in SQL:

0004-006 AlternateDenseRank

When I added this to my dataset and changed the Backgroundcolor property to :

= iif(Fields!GroupNr.Value Mod 2 = 0, “Silver”, “White”)

And hid the detail rows, the report finally looked how I needed  it.

0004-005 Alternate right
Now I was satisfied, although I could not help feeling a bit mixed with this ‘SQL’ like trick instead of using a SSRS function…

Leave a Reply

Your email address will not be published. Required fields are marked *