How to Create Alternative Row Background Colors in Ssrs for Values in a Group

SSRS Alternating Row Colors Within Groups

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

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

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.

Expression:

=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.

VB 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

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.

SSRS Alternating colors in groups

I use a function to control the background color - it's more reliable than using the ROWNUMBER function in SSRS.

You would add this to the VB Code section (Report Properties --> Code tab):

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 call the function from the BackgroundColor value:

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

All the function does is check the bOddRow variable and return the first or second color depending on bOddRow's value.

The third argument is the Toggle value. If it is 1, the color will change otherwise it would return the same color. The first column in a group would toggle while the others would have a 0 so the color is the same as the first.

The fourth argument is for the group number. For your report, I would make the inner Group 1 for the last number and the outer group would be 2.

Inner Group First column background color :

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

Inner Group Other columns background color :

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

Outer Group First column background color :

=code.AlternateColor("MintCream", "WhiteSmoke", 1, 2)

Outer Group Other columns background color :

=code.AlternateColor("MintCream", "WhiteSmoke", 0, 2)

Plagiarized from my previous post:
How to create Alternative Row Background colors in SSRS for values in a group

SSRS Alternating Row Colour Within Groups

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")

how to change row Background color in ssrs when all values in a group

Try this:

=SWITCH 
    (
    Fields!Type.Value = "Submitted - New Business" , "LightGrey",
    Fields!Type.Value = "Submitted - Renewals" , "LightGrey",
    Fields!Type.Value = "Submitted - ALL" , "Silver",
    Fields!Type.Value = "Quoted - New Business" , "LightBlue",
    Fields!Type.Value = "Quoted - Renewals" , "LightBlue",
    Fields!Type.Value = "Quoted - ALL" , "LightSteelBlue",
    Fields!Type.Value = "Bound - New Business" , "LightGrey",
    Fields!Type.Value = "Bound - Renewals" , "LightGrey",
    Fields!Type.Value = "Bound - ALL" , "Silver",
    Fields!Type.Value = "Declined - New Business" , "LightBlue",
    Fields!Type.Value = "Declined - Renewals" , "LightBlue",
    Fields!Type.Value = "Declined - ALL" , "LightSteelBlue",
    true, "Transparent"
    )

Or change "transparent" to "white", which works better with some output formats. Also, Make sure the field values don't have weird spaces or characters, like "Quoted - ALL " (space after "ALL"). That will mess up the equivalency test.

Add alternating row color to SQL Server Reporting services report

Go to the table row's BackgroundColor property and choose "Expression..."

Use this expression:

= IIf(RowNumber(Nothing) Mod 2 = 0, "Silver", "Transparent")

This trick can be applied to many areas of the report.

And in .NET 3.5+ You could use:

= If(RowNumber(Nothing) Mod 2 = 0, "Silver", "Transparent")

Not looking for rep--I just researched this question myself and thought I'd share.

SSRS Alternate Row Colour based on a value

=IIF(RUNNINGVALUE(Fields!RecId.Value, COUNTDISTINCT,"MainDataSet") MOD 2 = 0, 
   "LightBlue", 
    "Silver")

This worked for me when i wanted to do something similar.

The expression is giving every distinct name a row number and using the modulo operation. If the row number divided by 2 has a remainder (odd numbers) then the color will be Silver

---

Related Topics