Alternating backgroundcolor in tablix for rows of a group
I think all business users want to see alternating colors in on rows from your tablix. This is not a default function in reporting. After a evening of searching the internet and playing around i found the answer thanks to a post by Teo Lachev here: http://social.msdn.microsoft.com/forums/en-US/sqlreportingservices/thread/18b5a5f7-7304-4d61-821e-7faa344ce964/
How does it work:
The solution takes advantage of the expression evaluation rules in RS 2008 that have changed as a result of the new on-demand processing model. As a result, state in class-level instance variables is discarded as you page through the report which makes maintaining state trickier between page requests. However, RS 2008 introduces report variables that guarantee one-time evaluation semantics.
1. The report has a EvenRow code-behind function that toggles each time it’s executed.
2. In the Category group (double-click it to access its properties), a EvenRow group level variable is defined that invokes the EvenRow function once per each group instance.The rest is easy. I set the BackgroundColor property for each textbox to use this variable.
Practical:
- Go to report properties and then add the evenrow function to the Code box (just a simple state toggler):
Public _evenRow As Boolean
Public Function EvenRow() As Boolean
_evenRow = Not _evenRow
return _evenRow
End Function - Go to the row group properties you want to alternate, go to variables, add the variable “EvenRow” with the function “=Code.EvenRow()”, this will toggle each time the row is called upon
- Now go to the text box properties of the row in the group you want to alternate, go to fill, fill color and add the function “=IIF(Variables!EvenRow.Value=true,”Red”,”Green”)”
Thats it the background color will alternate each time a new group element is started and this is the result:
Thanks mate – this works a charm
Been searching for two days for a fix – works great, good simple instructions
Wow, thanks finally a no-nonsense way to make this work with multiple row groups!
thanks!
I got few other solution but all failed in the tablix with Grouping. Your solution worked out great. THANKS
This method fails when I sort my group by an aggregate such as [Sum(Revenue)]. The colors are there but they are all mixed up from row to row, like red-red-red-green-green-red-red. It looks like the group variable is set in order based on the grouped field, but when the sorting tab sets the group to sort in a different order, the group variable does not get recalculated and the row colors end up mixed up and out of order. Does anyone have a workaround for this?
Depending on the table structure (details vs. groups), there are two types of patterns to get this working. The patterns are described in detail in Robert Bruckners book (http://www.amazon.com/gp/product/0470563117), with downloadable sample reports (click on links):
• Green Bar Report, pages 85-92
• Alternate Group Shading, pages 93-99
Hi Kasper, Excellent article. thanks for posting such a valuable post.