SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Alternating Group Colors and Alternating Row Colors

Alternating Group Colors and Alternating Row Colors

After posting my recent blog about Alternating Group Colors, I’ve been asked several times about how one could also alternate the colors within the group. As such, I fired up Business Development Studio and made a copy of my Alternating Group Color report. I opened up my expression editor as shown in Figure 1 and tweaked it to what I have below.

=IIF(RunningValue(Fields!Category.Value,COUNTDISTINCT,NOTHING) MOD 2 = 0

,IIF(ROWNUMBER(NOTHING) MOD 2=0,"PaleGreen","White")

,IIF(ROWNUMBER(NOTHING) MOD 2=1,"White","PaleGreen"))

As shown in figure 1 below, select the FIELDS that you want to apply the alternating colors expression on (shown by the RED SQUARE). Then open the properties for your selection. Navigate to the Fill – Background Color property (shown by the YELLOW HIGHLIGHT) and enter the expression shown above.

Figure 1: Sample

Fields Selected for Expression 

Using the AdventureWorks database as my source and the above sample, you will have similar results as shown in Figure 3. I have provided both screenshots so that you can see a side by side comparison of alternating group colors.

Figure 2: Alternating Group Color Results (Zoomed Out)                                               Figure 3: Alternating Groups and Inside Rows

Alternating Group Colors Only                     Alternating Groups and Row Colors


For your convenience, I have included a zip file containing this example project. You can download it HERE.

Since I am writing this in order to help others and if you have found this article helpful, please rate it below (it only takes a few seconds). If you would like to provide constructive criticism to help me provide the information that you would like to see, I would love to hear that as well!

Until next time, “keep your ear to the grindstone” – Good Will Hunting


Brian K. McDonald, MCDBA, MCSD
Business Intelligence Consultant – Pragmatic Works Consultants

Email: bmcdonald@pragmaticworks.com | Blog: BI Developer Network

Convert with DTS xChange  | Develop with BI xPress  | Process with TaskFactory | Document with BI Documenter



Posted by cathy.hubin on 5 June 2014

This is excellent, exactly what I needed.  Thank you, Brian.

Leave a Comment

Please register or log in to leave a comment.