none
Alternating colors for gropus in an SSRS Report

    Question

  • Hi!

       I have a report with a table that has a single grouping.  Each group may have one, or two, or more rows.  I need all the rows in a group to be one color, but I need the color of the entire group to alternate from one group to the next, between gray and white.  I was able to do this in SSRS 2005 using RunningValue, but I can't figure out how to do it in SSRS 2008.

       Again, it really should be simple.  For Group 1, all rows in this group should white; Group 2, all rows in this group should  be gray;  Group 3, all rows in this group should be white, etc.

       To clarify; I am not trying to color alternate rows!!!  I know how to do that using row number.  I am trying to color all the rows in alternate groups.

      My group is called:  grpGroupID, if that helps. 

      What I need is the specific expression and where to place that expression.  That is, do I place the same expression in the detail columns as the groupby column? 

      There is no group header or footer.  The GroupID is the first column on the left and it is the Groupby field of the group.

    Group 1   134.89  Bicycle
    Group 2   123.12  Helmet
                    89.23  gloves
    Group 3   768.32  treadmill
                    78.09  monitor
    Group 4    33.23  step counter
    Group 5    19.99  earrphones
                     3.45  energy gum

    What I want is for group 1 to be white, both rows in group 2 to be gray, both rows in group 3 to be white, the row in group 4 to be gray, and the two group five rows, white.

    In other words,  Odd groups white, even groups gray.

    I've tried using the following expression by highlight the entire row and placing it in the Background color property, but the color isn't changing for any row.  It is remaining white.  I have the feeling ths formula isn't meant to control the color at the group level, but the rows within the group somehow, as though the countdistinct isn't really counting the distinct occurrences of the group-by value at the group level or something.

    =IIF(RunningValue(Fields!GroupID.value,

    CountDistinct, "grpGroupID") Mod 2, "White","Gainsboro")


    I'd really appreciate any help anyone can provide. Nothing I've tried is working.

    Thanks!
    Monday, June 08, 2009 4:50 AM

Answers

  • Karen

    Turns out that the technique I suggested earlier doesn't work consistently because the group headers may be added after the details.  You were on the right track with your conditional RunningValue technique but just needed to add a third argument to the RunningValue function.  This opens the scope to the entire dataset.  Try this to set the BackgroundColor property of each textbox in the detail row, including the group header textbox and all subordinate group fields.  Replace my Category field with the field you're grouping on:

    =IIF(RunningValue(Fields!Category.Value,

    CountDistinct, Nothing) MOD 2 = 1, "Gainsboro", "White")

    I'll put a sample report on my blog site under the post titled "Alternate Background Shading for Table Groups".  I hope this is helpful.


    Paul Turley [Hitachi Consulting] SQLServerBIBlog.com
    Tuesday, June 09, 2009 6:51 AM
    Moderator

All replies

  • Thanks . . .  But before I do this I have one small question . .   I don't have a group header . . .  The GroupBy field is actually on the row itself, as shown in the example.   How does that change any of your instructions?  I know you can't see it very well, but the Group by field, called GroupID, is a kind of "merged" field.  That is, when there are two or more rows, the GroupID spans all rows vertically.  The gray color on group 2 and group 4 should span the entire row, including the group by field that spans both rows.

    Group ID  Amt      Product
    ------------------------------------------
    Group 1   | 134.89  Bicycle
    ------------------------------------------ |
    Group 2  | 123.12  Helmet            |  This entire area is gray, including the words "Group 2" and the space below them as
                  |------------------------------ |  well as the two detail columns/rows
                  |  89.23  gloves               |
    ------------------------------------------ |
    Group 3   | 768.32  treadmill
                  | -----------------------------
                  | 78.09  monitor
    ------------------------------------------
    Group 4   |  33.23  step counter
    ------------------------------------------
    Group 5   | 19.99  earrphones
                  ------------------------------
                   |  3.45  energy gum
    ------------------------------------------


    Thank you so much!!!!

    Karen
    Monday, June 08, 2009 6:45 AM
  • Hi! Thank so much for your answer. I just need to clarify something . . . How do I do this when the grouped data has no group header? Please advise. Karen
    Monday, June 08, 2009 10:03 PM
  • You may have to play with this a little but you should be able to use the background color property of the "merged" in-line group header textbox for the toggle swtich and then the other textboxes for the other function calls.  Looks like a good blog post topic... I'll see what I can do.
    Paul Turley [Hitachi Consulting] SQLServerBIBlog.com
    Monday, June 08, 2009 10:17 PM
    Moderator
  • Karen

    Turns out that the technique I suggested earlier doesn't work consistently because the group headers may be added after the details.  You were on the right track with your conditional RunningValue technique but just needed to add a third argument to the RunningValue function.  This opens the scope to the entire dataset.  Try this to set the BackgroundColor property of each textbox in the detail row, including the group header textbox and all subordinate group fields.  Replace my Category field with the field you're grouping on:

    =IIF(RunningValue(Fields!Category.Value,

    CountDistinct, Nothing) MOD 2 = 1, "Gainsboro", "White")

    I'll put a sample report on my blog site under the post titled "Alternate Background Shading for Table Groups".  I hope this is helpful.


    Paul Turley [Hitachi Consulting] SQLServerBIBlog.com
    Tuesday, June 09, 2009 6:51 AM
    Moderator
  • I found this thread in my search for alternating row colors with a Tablix report. My report has several levels of groupings: SalesMgr > SalesRep > Customer > Invoice > InvoiceDetail and most of the solutions I've found will alternate row color, but the next row color is always the alternative row color relative to the last row color, so if there is an odd number of rows in a group, the report starts looking a little muddy. In order to always start the group on a white/transparent row and then start alternating row colors, I changed the statement just a little bit. Here's what I did:

    =IIF(Parameters!greenBarDetail.Value=True, (IIF(RunningValue(Fields!Invoice_lines.UniqueName, CountDistinct, "table1_Invoice") Mod 2, "Transparent", "AliceBlue")), "Transparent")

    Using "UniqueName" instead of "Value" seems to have corrected the issue and detail groups will always start on the transparent row. This particular example is a nested if; I have a parameter so users can choose to run with the greenbar effect or not. If you don't have or want to drive it off a parameter, just pluck the inner IIF-statement and put it where you need it.

    If this was helpful for you, vote for me for president... on second thought, don't. Name your first kid or a dog after me or just buy your spouse some flowers and tell them you love them.

    Peace.


    "A bus station is where a bus stops. A train station is where a train stops. On my desk I have a workstation..."
    • Proposed as answer by Henrov Wednesday, June 26, 2013 1:13 PM
    Tuesday, May 18, 2010 4:19 PM
  • I tried using

    =IIF(RunningValue(Fields!DepartmentName.Value,

    CountDistinct,Nothing) Mod 2, "Silver", "White")

    to alternate the colors of the rows in a matrix, it works fine where value is not 0, as soon as value is 0, it takes the color of preceding row. Does anyone know how I can fix it.

    Thank,

    Lyreb

    Wednesday, June 30, 2010 4:35 PM
  • My girlfriend says *thanx*     :)
    Wednesday, June 26, 2013 1:14 PM
  • Hi Lyreb,

    I am facing exactly the same issue as you. Did you resolve it? If so, can you please let me know how?

    Thanks.

    Tuesday, October 29, 2013 10:36 PM