Tuesday, June 17, 2008

Reporting Services matrix alternating column colors

Since the matrix has dynamic columns the value for its background color has to be calculated rather than simply applied through the tools GUI. I figured it would be simple using RowNumber(Nothing), but I got this error when I tried:

Error 1 ... The BackgroundColor expression for the textbox ... has a scope parameter that is not valid for RunningValue, RowNumber or Previous. The scope parameter must be set to a string constant that is equal to the name of a containing group within the matrix ...

After fighting with it for a bit I searched msn and found this:

[quote]
The RowNumber function can’t be used in the whole matrix. But it can be used in the groups. So please replace the nothing with the name of the rows group. For example: =IIF(RowNumber(“rows_group_name”) Mod 2,"gray","white")
[/quote]

That ended up working great for me, however it appears that if you have a more complicated matrix you might need to take some additional tips from this page:
Green-Bar Matrix

4 comments:

  1. Nice work!!! I searched for quite a bit before finding this tip!!!

    ReplyDelete
  2. Thanks, it's nice to know when people like something they read.

    ReplyDelete
  3. thanks for this! i've been searching for it! :)

    ReplyDelete
  4. Hey Ning, thanks for writing.
    I'm glad I'm not the only one who benefited from this information.

    ReplyDelete

Please leave your thoughts, I love hearing what you got out of the post. Spam comments will be removed.