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).
Normally I would use an expression like “= iif(RowNumber(Nothing) Mod 2 = 0, “Silver”, “White”)” for the ‘backgroundcolor’ property.
This would give a nice simple row coloring like:
(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.
You can add a scope to the expression, but that only works for making the rownumbers start from 1 on a new group.
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.
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:
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.