Alternating colors in groups in Microsoft SQL Server Reporting Services

In this post I will present you a one of the ways of defining alternating row colors in group details. Recently I had to apply such thing in one of the projects I am working on and as it turns out, there are quite a lot instructions, guides, advices, etc. So I will try to combine and evaluate them as there are some flaws in some of them. First of all, see the end result I am trying to achieve:

1First row is the text field which I am grouping on, the second filed is the ID from the source dataset and the third row is the color, that the row must be.

1. Applying alternating color on detail rows of group using RunningValue() and CountDistinct()

First method I tried was using RunningValue() with the following syntax:

=IIF(RunningValue((Fields!DatasetColumn.Value), CountDistinct, Nothing) Mod 2, "White", "WhiteSmoke")

This is a very good method but it has one major flaw – when grouping over a text field, which contains data differing only in letter case, then those values are in a single group, but the RunningCount in combination with CountDistinct returns values, that are messing up the coloring. What do I mean – in our example if I use this method for coloring the result is the following:

2 

As it turned out (and as I mentioned before), when Reporting Services are grouping text fields, it does not matter what the letter case is. I could not find any reference in MSDN on the algorithm of grouping so it is kind of a mystery to be revealed.

2. Applying alternating color on detail rows of group using RunningValue() and Sum

The next method I tried was using Sum and Running Value. The syntax is as follows:

=IIf(RunningValue(1, Sum, "Group_Name") Mod 2, "White", "WhiteSmoke")

Unfortunately the result was the same

3. Applying alternating color on detail rows of group using RowNumber()

This is most probably the most universal function that will help you on applying alternating row colors. Of course you must be very careful when specifying the scope over which the RowNumber() function will “act” The syntax is:

=IIF(RowNumber(GroupName) Mod 2, "White", "WhiteSmoke")

And this did the trick.

So if you ever need to apply alternating colors, have in mind that you can do it over group details just as easy as doing it on the whole dataset!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s