Pages

Thursday, 29 September 2011

Alternate Colors in a Matrix/Pivot Table

To add alternate colors in a matrix the expression should be as follows for the row group:


=IIf( RunningValue (Fields!FIELDNAME.Value, CountDistinct, Nothing) MOD 2, "Transparent", "aliceblue")


The tricky bit is when you want the same shading for the column groups. The way to do this is to insert a column outside of the row group and then use the same expression. Change the name of the textbox to "color". Then in your column group or data field in your matrix in the background color insert the expression which refers to the textbox "color":
=Reportitems!color.value
The colors in the matrix will alternate like a normal table.

Wahayy!!

Also in a anormal table where you have multiple groups you can use the following script for say 2 groups I assume you just use '&' and add in the extra fields if you have more than one group:

=IIF(RunningValue(Fields!Field1.Value & Fields!Field2.Value,CountDistinct, Nothing) MOD 2 = 1, "White", "Aliceblue")

4 comments:

  1. Thank you Thank you Thank you. I have been looking at the top solution forever trying to get it to work on multiple groups and it just does not. Thankfully you also provided a solution to multiple groups!! I could not seem to find anyone who addressed that issue and it works great!

    ReplyDelete
  2. Thanks, your example was very helpful

    ReplyDelete