posted 6/4/2010 by briankmcdonald - Views: [24644]
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
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
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, MCSDBusiness 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
Hi Brian,
Your method is great for a simple report that doesn't use a matrix/tablix. With a multi dimensional source and a dimension on the columns you can run into trouble, because SSRS treats the rownumber() not by what is displayed but by the order of the tuples returned from SSAS.
There is a method that uses report level custom code to create an EvenRow() function, then a row group variable that references the function and finally sets the text box shading property based on the variable value.
Have a look at this link http://www.powerpivotblog.nl/alternating-backgroundcolor-in-tablix-for-rows-of-a-group
Karl Beran
Senior Business Intelligence Consultant - IMGroup UK
http://karlberan.spaces.live.com/
Karl,
As we all know, there are many ways to "skin a cat" and my example is no different. Of course you can programmatically modify the report and I have many times in the past, but my post was simply just showing one method of delivering the desired result. As far as the linked example, I would like to see what would happen if there were more than one group? I would suspect that all of the groupings would be WHITE and only the colors within the group would alternate. My example was to show multiple groups and alternating the colors within those groups AND the rows inside each group.
Thanks,
Brian
This works for aggregate columns as well. Thanks Brian!
Cam
Glad you liked it Cam!