Alternating backgroundcolor in tablix for rows of a group

By | June 5, 2009

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

  • Josh Ashwood

    Thanks mate – this works a charm 😀

  • Jerry Fullwood

    Been searching for two days for a fix – works great, good simple instructions

  • k2

    Wow, thanks finally a no-nonsense way to make this work with multiple row groups!

    thanks!

  • Ramesh

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

  • Eric S.

    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?

    • Kasper de Jonge

      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

  • Durga

    Hi Kasper, Excellent article. thanks for posting such a valuable post.

  • Kamran

    Thank you. Thank you.

  • Ajay

    Just brilliant. Thanks for the help!!

  • Josh Ashwood

    Even after three years, this is the one I keep coming back to.

  • Pam

    Excellent: simpe and fast! I have been searching for this for awhile. Thanks for publishing it!

  • Alpesh

    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

  • Thanks very useful. Worked pretty fine!

  • Nice. Thanks!

  • thanks for sharing this