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.
  
• Now the alternated row color shows up correctly.

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.

Reference:

http://social.technet.microsoft.com/Forums/en-US/sqlreportingservices/thread/6dbde4cf-2b3f-4aa6-b47c-5c37c47bfc82#72a83b7f-241c-4c80-b79a-7f6e4b4bc74a

http://msdn.microsoft.com/en-us/library/dd255285