Oleg Oleg - 9 months ago 45
SQL Question

How to create Alternative Row Background colors in SSRS for values in a group

I am using this expression to get alternative colors for my row group:

=iif(RunningValue(Fields!Status_Reason.Value,CountDistinct,Nothing) Mod 2, "LightBlue", "White")

And everything works fine except for some rows:
enter image description here

I am assuming its because of some values are "-", which are 0.
What could be the work around in this situation?
This is my groups:
enter image description here


I use some VB code for Alternating the row color. It's a little more work to set up at first, but it always works right and you can re-use the code in other reports by copying the VB code.


=code.AlternateColor("AliceBlue", "White", 1, 1)

=code.AlternateColor("AliceBlue", "White", 0, 1)

The first column should have the first expression - the first 1 in the argument tells it to change color. The remaining columns use the second expression with the 0 indicating that the color won't change.


Private bOddRow(10) As Boolean 

Function AlternateColor(ByVal OddColor As String, ByVal EvenColor As String, ByVal Toggle As Boolean, ByVal Type AS INTEGER) As String 

  If Toggle Then bOddRow(Type) = Not bOddRow(Type) 

  If bOddRow(Type) Then 
                Return OddColor 
                Return EvenColor 
  End If 

End Function

If you have multiple levels of grouping in one table, you would change the second number of the expression so the rows are unique for each group. In the below example, the main grouping is colored in white and AliceBlue and the sub group is whitesmoke and a lighter blue.

enter image description here