http://www.sqlservercentral.com/blogs/briankmcdonald/2010/06/04/alternating-group-colors-and-alternating-row-colors/

Printed 2014/09/16 07:50AM

Alternating Group Colors and Alternating Row Colors

By Brian K. McDonald, 2010/06/04

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

 


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.