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)

image

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)

image

Dynamic Column Group Scenario

image

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.

image

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


image



  • 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

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

image



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


image




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


image


image



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