Saturday, July 14, 2012

Alternate Row Color for SSRS Report With Column Group

There are several ways to alternate background color for rows. The most common way is using RowNumber() function to retrieve the order of the row then set the background color using expression as:

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


If there is a row group, using RowNumber() can lead to some issues. The another alternative is to use RunningValue() function as:

=IIf(RunningValue(Fields!Product.Value, CountDistinct, "Customer") Mod 2 > 0,"Silver", Nothing)


Dynamic Column Group Scenario


When there is a column group with dynamic data (i.e. Sales Reason) , you will not be able to use above solutions to alternate the row color.  Since the number of column (the Sales Reason in my example) may be varied depended on the row group data (the Date and Product in my example).  Here is what you may see if you use above approach only.


Solution 1

Use Customer Code and variable to determine the row color

  • Create a report customer code to toggle the row status for each function call
Public RowStatus as Boolean = False
Public Function GetRowStatus as Boolean 
    RowStatus  = Not RowStatus 
    Return RowStatus 
End Function

  • Add a variable RowColor to the detail row group that has value set to the expression as below to call the customer function:

          = Code.GetRowStatus()


  • Select the whole detail row and set the background color based on the variable value.image
  • Now the alternated row color shows up correctly.image


Solution 2

Store RunningValue() in a textbox and use it to set the row color

  • Add a new column outside the Column group at end of the report


  • Name the detail row textbox as RowNumber and set its value to the expression as

        =RunningValue(Fields!Product.Value, CountDistinct, "OrderDate" )

  • Select the whole detail row and set the back ground color to use the expression as:

         =IIF(ReportItems!RowNumber.Value mod 2 > 0, "LightGrey", Nothing)


  • Now the row color displays correctly and we can hide the last column that contains the RowNumber text box from the end user.





  1. This saved me a ton of headaches, thank you very much!

  2. Wow, I wish I found this blog ealier today, I wasted about 8 hours trying to get this right! Thank you so much for this!!!

  3. Hi Fendy
    The custom code saved my life :) :)
    I was banging my head to do this for a whole day.
    Thank you very much.I would have found this earlier
    nevertheless thank you

  4. brilliant solution and well written !!
    easy to follow and implement.
    thank you very much :)

  5. This is a great and useful post, thank you very much for sharing.

    I am trying to figure out how to work this into a "=Switch(..)" expression. I have 4 groups (4 colors) and the zero values leave white blanks all over my report.. I'll keep working at it but I am sure someone out there knows a solution. . .