SSRS Alternating Row Colour Within Groups

10
votes

I have a ssrs table report with row grouping and I would like to know how to change the colours of rows in groups without changing the background colour of the group column itself. With the answers I have found and implemented I get the effect of the second table in the picture when I want the effect of the first table:

enter image description here

Any help will be much appreciated.

This is a picture of the actual report and it's grouping:

enter image description here

2
sqlreporting-services
Looks like you have just got the scope wrong to the rownumber function. What are the names of the row groups? What expression are you using to set the background colour? – Martin Smith
Also it isn't clear from your example does every new group start with a white row or do you want it to,follow on opposite to the last one in the previous group? – Martin Smith
It is fine if every group starts with a white row, but if it's possible to follow on opposite to the last one in the previous group then that would be first choice. Some of the expressions I used I got from other links like: IIf(RowNumber("TheNameOfYourGroup") Mod 2 = 0, "White", "WhiteSmoke") And: = IIf(RowNumber(Nothing) Mod 2 = 0, "Silver", "Transparent") And some vb functions that I found aswell – Liam Domingo
Strange, TBH I'm not sure why those aren't working for you. I can't reproduce this. What is the structure of the report? – Martin Smith
It's a normal table report with two groups, the one is not visible and the other one is the Area which is visible – Liam Domingo

2 Answers

22
votes

The RowNumber technique only works on the Details group i.e. the lowest level group (with no Group columns defined). Think of it as returning the Row in the Dataset.

Instead I would write an expression that calculates the equivalent to RowNumber, but for the Group level - typically something using RunningValue ... CountDistinct on the Group Key field, like this:

= Iif ( RunningValue ( Fields!tableid.Value , CountDistinct , "TheNameOfYourGroup") Mod 2 = 0, "White", "WhiteSmoke")

0
votes

I need to apply the alternation to all rows including Total rows so:

'define Report Variables: ActiveRowColour, ColourA, ColourB
'Call this function in one cell per row by setting => BackgroundColor=Code.NewActiveRowColour(Variables!ActiveRowColour.Value)
'Then All other cells on that row set -> BackgroundColor=Variables!ActiveRowColour.Value
Public Function NewActiveRowColour(ByVal sActiveRowColour As String) As String
	If sActiveRowColour = Report.Variables!ColourA.Value Then
		Report.Variables!ActiveRowColour.Value = Report.Variables!ColourB.Value
		Return Report.Variables!ColourB.Value
	Else
		Report.Variables!ActiveRowColour.Value = Report.Variables!ColourA.Value
		Return Report.Variables!ColourA.Value
	End If
End Function