SSRS - Ignore nulls when doing alternate row color (in matrix)

0
votes

usually outside of a matrix column group I would use =IIF(RowNumber(Nothing) Mod 2 = 0, "Transparent", "LightGrey") to alternate row colors.

But when using a matrix column group the same expression alternates the columns in that group instead of the rows, so have used this instead =IIF(Runningvalue(MAX(Fields!My_column.Value),CountDistinct,"my_column_group") mod 2,"Transparent", "LightGrey")

The problem now is that when there is a null or blank value it ignores it and the row colours do not alternate, as demonstrated in the photo enter image description here

Is there a way to include a ways of ignoring NULL values in the expression?

1
reporting-services

1 Answers

1
votes

This is a little messy as it means adding a new column and then setting the width so you cannot see it but it does work.

  1. In your matrix add a new column inside your row group.
  2. Make a note of the name of the textbox that was just created or rename it to something better (in my case I called it bgCol)
  3. Set the expression for this textbox to =IIF(RowNumber(Nothing) Mod 2 = 0, Nothing, "LightGrey")
  4. Click the row header so the entire row is selected and set the backgroundcolor property to =ReportItems!bgCol.Value (or whatever the new textbox is called)
  5. Shrink the column width to it's minimum so you can't see it.