SSRS Alternating Row Colors Within Groups

Question

I have some issues getting alternate row colors.

I've tried different expressions and this is as close as I've gotten to getting it done:

=IIF(RunningValue(Fields!agent_name.Value,CountDistinct, Nothing) MOD 2 = 1, "Gainsboro", "White")

Where all other of my reports are getting correct alt row shading, I'm having trouble with this specific report that has a Row group and a Column group.. Spent about 2 days and still no luck :(

When I make my rows with the above expression, it is displayed like this:

My Row group obviously is the name,

Column group is the Month/year.

Any suggestions / assistance would be greatly appreciated

Answer 1

The problem is that there are NULLs in the data where the matrix is creating cells where there is no data. Since there's no Agent_Name associated for those cells, they default to the white.

I gave up on using Runnning Values and Row Numbers in SSRS and usually use the Alternating Row Color function.

You add some VB Code to the report (Report properties > Code):

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 
  Else 
                Return EvenColor 
  End If 

End Function

Then use an expression to populate the colors:

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

The first two arguments are the colors to alternate between, the third argument is the control that tells it to switch colors, and the fourth argument is for the group to be used for nested grouping.

The first column of the row has the control with the first 1.

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

Use this in your first column - where you have your Agent Name.

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

Answer 2

Necromancing.
The accepted answer didn't work for me, as the Toggle is very irregular within the column-grouping.

However, the below code worked.

You need to set background-color in the first cell as

=iif(Code.IncrementRunningValue() Mod 2 = 0, "WhiteSmoke", "White")

And in all subsequent cells as

=iif(Code.GetRunningValue() Mod 2 = 0, "WhiteSmoke", "White")

Setting it as group variable will not work, because the sorting is applied after the grouping (which means the rows are re-arranged after the values have been generated).

Private m_s_count As ULong = 0

Public Function IncrementRunningValue() As ULong
    m_s_count = m_s_count + 1UL

    Return m_s_count - 1UL
End Function

Public Function GetRunningValue() As ULong
    Return m_s_count
End Function

---

Or you can do it even simpler:

Private m_s_AlternatingColor1Count As ULong = 0


Private Function ComputeAlternatingColor1(val As ULong) As String
    If val Mod 2 = 0 Then
        Return "WhiteSmoke"
    End If

    Return "White"
End Function


Public Function IncrementAlternatingColor1() As String
    Dim alternatingColor As String = ComputeAlternatingColor1(m_s_AlternatingColor1Count)
    m_s_AlternatingColor1Count = m_s_AlternatingColor1Count + 1UL

    Return alternatingColor
End Function

Public Function GetAlternatingColor1() As String
    Return ComputeAlternatingColor1(m_s_AlternatingColor1Count)
End Function

and then in the first row's background-color:

=Code.IncrementAlternatingColor1()

and all subsequent rows' background-color:

=Code.GetAlternatingColor1()

That has the advantage that you can switch the colors in ONE place (DRY).