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:

 tablix

15 Replies to “Alternating backgroundcolor in tablix for rows of a group

  1. I got few other solution but all failed in the tablix with Grouping. Your solution worked out great. THANKS

  2. 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?

    1. 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

  3. try with custom code
    =IIF(Code.IsEvenRow(Fields!HalfHour_301.Value),”#F3F3F3″,”White”)

    Try with custom code

    =IIF(Code.EvenRow(Fields!Date.Value),”#F3F3F3″,”White”)

    Public _EvenRow As Boolean
    Public _HalfHour As String

    Public Function EvenRow(ByVal defaultValue As String) As Boolean
    IF _HalfHour=defaultValue then
    return _EvenRow
    ELSE
    _HalfHour=defaultValue
    _EvenRow = Not _EvenRow
    END IF
    return _EvenRow
    End Function

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.