How to alternate row color with multiple groups?

Question

I have a report which has threee (3) row groups: Region, City, Type. I need to alternate each row color frow Gainsboro to White and am having no luck. I use the following expression for background color property for "type" row group (which I found while googling, but this DOES NOT WORK):

=iif(inscope("type"),iif(rownumber(nothing) mod 2 = 0, "Gainsboro", "White"),"White")

A sample of my report:

Region 1 (region group)  ----- Background color=WHITE

 Dallas TX (City group)    ----- Background color=GAINSBORO

     Medical (Type group)   ----- Background color=WHITE

     Surgery (Type group)   ----- Background color=GAINSBORO

 San Antonio (City group)   ----- Background color=WHITE

    Surgery (Type group)    ----- Background color=GAINSBORO

etc.

Will somebody please create a background expression (and what row would I place it on) so each backgroup row alternates colors between Gainsboro and White?

Thanks.

Dave

Answer 1

Hi Dave,

Try below links,

http://social.msdn.microsoft.com/Forums/en/sqlreportingservices/thread/e232e21e-955b-4b65-8231-c2965255ce3d

http://www.bidn.com/blogs/briankmcdonald/bidn-blog/717/alternating-group-colors-and-alternating-row-colors

http://www.thesqlbloke.com/ssrsalternate-row-colors-of-a-group/

http://kylefinley.net/archive/2006/07/06/226.aspx

---

Thanks & Regards, sathya

Answer 2

Hi Sathya,

Thanks for the links, but none quite fit the problem I have. I tried Paul's expression from the first link

=IIF(RunningValue(Fields!city.Value,

CountDistinct

, Nothing) MOD 2 = 1, "Gainsboro", "White") placed in all the city group rows and also

=IIF(RunningValue(Fields!type.Value,

CountDistinct

, Nothing) MOD 2 = 1, "Gainsboro", "White") place in all the type group rows.

The resutls were not what I needed as not every row alternated colors.

Any suggestions?

Dave

Answer 3

Hi Dave,

According to your description, you want alternate row color in your tablix, right? If you use a table in your report, you can use RowNumber function to alternate row color. The expression in would like:
=IIF(RowNumber(nothing) mod 2 =0,"Gainsboro", "White")

If you use a matrix in your report, the RowNumber function in the Matrix is totally different with it in the table because you are always dealing with grouped data in a matrix. In this case, please refer to the steps below to alternate row color.

1. Add the custom code below to your report:
   dim Counter as integer=0
   Public function getCounter() as Integer
    Counter=Counter+1
    return Counter
   end function
2. Add a column to the right of the matrix, type the expression below to inserted column:
   =code.getCounter
3. Change the text-box name to “rownumber” and set the visibility of inserted column to “Hidden”.
4. Use the expression below to set the background color:
   =iif(reportitems!rownumber.Value mod 2 = 0,"Red","Green")

The report looks like below:

If you have any questions, please feel free to ask.

Regards,
Charlie Liao

---

Charlie Liao
TechNet Community Support

Answer 4

Hi Charlie,

Yes, I have a matrix with three different row groups.

Does it matter where I place the inserted column, i.e., inside or outside of the grouping? No matter where I inserted the new column, I could not get your suggestion to work.

In your matrix example, my three (3) groups would be: Sales Territory Group, Sales Territory Country and Business Type. Based on that, how would I incorporate your suggestion to fit my needs?

Also, when I typed the expression into the inserted column: =code.getCounter, there was a red underline underneath ".getcount "; hovering over the expression, the message read "Unrecognized identifier". Is this OK?

Thanks again.

Dave

Answer 5

Hi Dave,

We should add a new column outside the group because we use this column to count the row number.

Regards,

---

Charlie Liao
TechNet Community Support

Answer 6

Hi Charlie,

I waws able to get your code to execute, but it is only changing the "type" row group, I need the entire tablix to produce alternating background row color.

Your suggestion gives me the following background color scheme:

Region 1 (region group)  ----- Background color=WHITE

 Dallas TX (City group)    ----- Background color=GAINSBORO

     Medical (Type group)   ----- Background color=GREEN

  San Antonio (City group)   ----- Background color=WHITE

    Medical (Type group)   ----- Background color=RED

    Surgery (Type group)    ----- Background color=GREEN

  Amarillo (City group)   ----- Background color=WHITE

    Medical (Type group)   ----- Background color=RED

    Surgery (Type group)    ----- Background color=GREEN

What I need is EVERY ROW (regardless of group) to alternate background row color.

Ex)

Region 1 (region group)  ----- Background color=WHITE

 Dallas TX (City group)    ----- Background color=GAINSBORO

     Medical (Type group)   ----- Background color=WHITE

  San Antonio (City group)   ----- Background color=GAINSBORO

    Medical (Type group)   ----- Background color=WHITE

    Surgery (Type group)    ----- Background color=GAINSBORO

  Amarillo (City group)   ----- Background color=WHITE

    Medical (Type group)   ----- Background color=GAINSBORO

Is this possible?

Thanks again for your help.

Dave

Answer 7

Can anybody modify Charlie's code or come up with a solution to solve my problem?

Thanks.

Dave

Answer 8

Please provide us a screenshot about the result which you can do it on EXCEL, so that we can make further analysis.