Thursday, September 18, 2008

How to alternate colors in a Matrix even when RowNumber doesn't work

Most of the time you can easily alternate colors using:
Table: =iif(RowNumber(Nothing) Mod 2,"Green","White")
Matrix: =iif(RowNumber("Matrix_RowGroup") Mod 2,"Green","White")

The above is by far the most popular method of accomplishing the task, it is widely known and very simple.  However it has one major downfall, when the matrix doesn't receive any data for a specific cell it must generate a blank cell, when that happens the RowNumber count does not get auto incremented which can really throw the coloring off.

Chris Hays came up with a great workaround hack to this issue.  You can create a static grouping at the lowest level and use RunningValue to calculate an alternating value for the group header cell, then hide the cell and point the data cells BackgroundColor to the value of the group header cell.
=iif(RunningValue(Fields!Country.Value,CountDistinct,Nothing) Mod 2, "AliceBlue", "White")

I would highly recommend visiting Chris's blog where he gives step by step instructions on how to accomplish the task.

1 comment:

Darryl said...

I found an alternate solution to the matrix color scheme using the original dataset (for 2005 and later):

1. Create a color value column/field in the dataset for each group using DENSE_RANK
Example: For [postal code] values, the column will be
bgcolor = CASE dense_rank() OVER (ORDER BY [postal code]) WHEN 1 THEN 'White' ELSE 'LightGrey' END

2. on the "postal code" group on the matrix, set each textbox bgcolor property to "=First(Fields!bgcolor.Value)"

This works for most cases provided each group contains a unique value.

(text copied from an earlier post I made on: http://weblogs.sqlteam.com/joew/archive/2008/08/20/60691.aspx)