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

11 comments:

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

    ReplyDelete
  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!!!

    ReplyDelete
  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

    ReplyDelete
  4. Good post, save lot of time! Thanks!

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

    ReplyDelete
  6. 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. . .

    ReplyDelete
  7. I used solution a variation on solution #1 that I thought I would share. The table I wanted to have alternating colors on was inside of another group (one level up). This higher-up group has a page break on each new instance.

    The result was that with Solution #1, the color of the first row on the second page was dependent on the color of the last row on the first page. That is, an odd or even number rows on page 1 effects the color of the first row on page 2.

    So I created another group variable on the higher-up group, which calls another function in the custom code. This function returns a meaningless value to variable (which seems to be a requirement), but it also resets the "RowStatus" Boolean in the first function to false. The result is that on each iteration of the higher-up group, the Boolean value for the first row of the inner group is re-initialized.

    It seems like a bit of a hack to me, since I'm using a variable to execute code, rather than store and retrieve a value. But it works!

    ReplyDelete
    Replies
    1. Thank you for this idea! Solved my problem quite well!

      Delete
  8. This is good solution.
    Can you handle column grouping like 2 column group in fist row then next 2 is in second row and so on , Please if you can then give me solution .

    ReplyDelete
  9. This was an awesome solution!!! thank you so much!!!

    ReplyDelete
  10. It's really good post because it helpful for learners. check it once through MSBI Online Training Hyderabad

    ReplyDelete