SSRS how to remove hidden row from alternating background color

Question

I am using this for my alternating background colors

IIF((RowNumber(Nothing) Mod 2),"GAINSBORO", "WHITE")

but I need to hide one of my rows in the Tablix which then causes this to happen

What's the best way to continue the alternating color of rows with the hidden row?

How are you hiding the row in your table? Are you using a filter for it, or some other method to change the visibilty? — Jonnus
I'm right clicking on the Tablix row and selecting Row visibility... then I added my expression to hide the row based on condition of the year parameter. When the year changes to 2015 the row becomes hidden and thus the alternating background colors get jacked up. — TechGrl1
As a general comment it would be much more efficient to filter your table. Right click the tablix, select filtering, and year = 2005. Using this method your existing calculation should work — Jonnus
Tried the filter but I have nested Groups within the Tablix. messes up the data view. — TechGrl1

Jonnus Jonnus · Accepted Answer · 2015-11-10T09:17:12

Based on this answer to another question I have another method for alternating the row colours, this time taking the hidden row into consideration.

You need to add the following Custom Code to the report

Dim CurrentColour As String
Dim LastRowNumber AS Integer

Function SwitchColour(ThisRowNumber As Integer) As String
    If CurrentColour = "" Then
        CurrentColour = "Red"
    End If

    If LastRowNumber = 0 Then
        LastRowNumber = ThisRowNumber
    End If

    If LastRowNumber <> ThisRowNumber Then
        ' Change the colour
         If CurrentColour = "Red" Then
            CurrentColour = "Yellow"
        Else 
            CurrentColour = "Red"
        End If
        LastRowNumber = ThisRowNumber 
    End If

    Return CurrentColour 
End Function

This is effectively saving a value for the CurrentColour to persist across the elements in the report. When the SwitchColour function is run it determines if the row number has changed (LastRowNumber <> ThisRowNumber), and if so changes the colour for the cells.

Using this sample data as “DataSet1”

Val | Colour
----+-------
36  | Red
22  | Red
55  | Green
23  | Red
74  | Red

And setting the cells BackgroundColour to

=Code.SwitchColour(RowNumber(“DataSet1”))

Give this result

When you then apply a visibility expression to the Row to negate the value “Green” for example using this expression

=iif(Fields!Colour.Value = "Green", True, False)

The result is like this

Note the colouring still changes, despite there being a record absent from the middle of the table

Is this the sort of behaviour you would like, and is it something you can apply to your project? If you need further assistance, please let me know.

SSRS how to remove hidden row from alternating background color

2 Answers