SSRS Alternating Row Colors in Matrix

Josh,

So you've added a Matrix in SSRS and want to have alternating row colors.  You've found the RunningValue function useful, but when there isn't data in some of the columns, the alternating colors start getting wacky:

Here's what you did to get the row colors looking right...

The Function
Before we go into the final solution, let's go over the RunningValue function.  Changing the value of the background color programmatically is pretty straight forward.

1. Select the text box(es) to change
2. Open Expression Dialog for Background Color
1. Properties (F4)
2. On Background Property, select the dropdown
3. Select "Expression"
3. Add the expression to determine the background color

The Expression:

=IIF( RunningValue( Fields!MyRowGroup.Value, CountDistinct, Nothing ) Mod 2 = 1, “White”, “LightGrey” )

The key to the expression here is the RunningValue function.  The first argument is the expression for the aggregation, in this case it will be the row group value.  The second argument is which aggregation function do we want to run.  In this case we are doing a count distinct, which will only increment once the value changes.  Behind the scenes in the Matrix, the Group contains multiple rows so a RowNumber function would not consistently give us what we want.  The third argument is the scope of the aggregation, and generally can just be set to "Nothing".

The rest of the Expression in the IIF statement calculates the modulus (remainder) of the RunningValue expression and compares it to 1.  If it is even (modulus = 0) then the IIF will evaluate to false ("LightGrey"), but if it is odd (modulus = 1) then the IIF will evaluate to true ("White").  What we end up with is a consistent alternating row based on the row group of the matrix.

But wait, there's a catch...

As demonstrated in the first screen shot, when some data points are missing in the column of the matrix, the CountDistinct doesn't increment until the next value is seen.  This causes the inconsistent background coloring.

To fix this, I came across this blog post which has the following solution.

Final Steps

The idea to the solution is that the group totals of the matrix are consistently what we want, so we need to utilize the state of the expression in that scope.

1. Add a new group total column
2. Name the textbox of the row to something easy to reference (i.e. "RowColor"); Properties -> Name;
3. In the textbox add the same expression used above; when evaluated, the RowColor textbox will display either "White" or "LightGrey" for that row
4. Select the matrix column(s) that was giving us issues; this could be done for all columns as well.
5. For the background expression enter "=ReportItems!RowColor.Value"
6. Hide the RowColor column
1. Right click on the column selection object and select "Column Visibility"
2. Set the Visibility option to "Hide"

Now the alternating row colors are working as expected!!