Thursday, June 24, 2010

SSRS-Backgorund Colors-Group Variable...

I was recently working on creating a SSRS report in SQL Server 2008 which required alternating background colors. Initially it seemed a easy requirement but as the report got built out it had a row group and a column group which made the getting background colors tricky. Initially I went with the RowNumber and Running value functions, it did not get the required effect. In an earlier blog post i had mentioned the use of DENSE_RANK() function in a stored procedure to get the background color effect. I recently came across another approach which is using Group variables(I would like to acknowledge the MSDN forums and blogger's who have mentioned this approach), which works very well. First you would need a custom code function which would go in the Report Properties code section:

Public EvenRow as Boolean
Public Function AltRow() as Boolean
EvenRow = Not EvenRow
Return EvenRow
End Function

Once this is done, In the group properties (this would be the data point/column in the dataset for which we need the alternate row color), under variables create a variable called AltRowColor, in the value section call the function =Code.AltRow()

Select the whole row for which you need the alternate row color, in the background color property enter the following expression: =iif(Variables!AltRowColor.Value,"Gainsboro","White"). Once this is completed run the report you should see the alternate row colors. This was done in SSRS 2008 with the tablix control which has Row and Column Groups.

13 comments:

  1. Thank you for posting this valuable information. I implemented and it worked great!

    ReplyDelete
  2. Thank you all for your comments.

    ReplyDelete
  3. 2 years later and just as useful, had spent hours on this. Clean and simple, perfect!

    ReplyDelete
  4. Thank you @Anonymous. This is one area where i feel SSRS as aproduct could improve upon grouping and background colors. It is one of the most requested requirement in a report.

    ReplyDelete
  5. thanks You so Much.it solves my problem

    ReplyDelete
  6. Thanks you soo much..it works fine

    ReplyDelete
    Replies
    1. Thank you Vadei, glad it worked out for you.
      Thanks for reading my blog.

      Delete
  7. i tried everything else nothing works and this on works so good .Thanks you so much. nice and simple.

    ReplyDelete
    Replies
    1. Thanks Drashan, Glad it worked out for you.
      Thank you

      Delete
  8. perfect, very useful post. thanks a million!

    ReplyDelete
  9. Does not work at the Report Level
    * Report Properties / Variables:
    * Tax = 0.08 [read only]
    * Report Properties / [Custom] Code:
    Public Shared Function functionTax(ByVal param as Double) as Double
    Return param * Variables!Tax.Value
    End Function

    ReplyDelete