none
Alternate row color for matrix where i have Row group and colum group

    Question

  • Hi,

    I am using matrix in my report which is having one Row Group called ROW_GROUP and one colum group called COLUMN_GROUP. i need to give alternate color for row. I am using this expression "IIf(RUNNINGVALUE(Fields!ROW_GROUP_COLUMN.Value,CountDistinct,Nothing) MOD 2, "White","#f6fb72")" but it is not working out. please anyone can help me on this issue.


    Thanks Shiven:)
    Thursday, April 22, 2010 6:47 AM

Answers

  • Hi S Kumar Dubey,

    Your issue is obviously cased by the Scope of the RunningValue function,we can achieve this in Sql Server Reporting Service 2008 using your expression.But in Sql Server Reporting Service 2005,we should select every textbox to set its background color with the right Scope rather than the whole row.See this picture:

    1. Click the Red circle and press 'F4',Property window will be displayed in your right side

    2. Find out the BackgroundColor property,and then type in the expression =IIF(Runningvalue(Fields!Category.Value,CountDistinct,"matrix1") mod 2,"Red","Lime").

    Note:matrix1 is the name of your matrix

    3. Click the green circle and press 'F4',type in the expression in the backgroundcolor property =IIF(Runningvalue(Fields!Category.Value,CountDistinct,"matrix1_Year") mod 2,"Red","Lime")

    Note:matrix1_Year is the name of the column group.

    Below picture is produced by the above steps, you can set your color depending on requirement.

    If you have any question,please feel free to ask.
    Thanks,
    Challen Foo

     

    Tuesday, April 27, 2010 6:02 AM
    Moderator
  • Hi All,

    My problem got resolved.

    I used below Method:

    I created a dummy column out of Column group. There in all Text box for dummy column, i gave this alternate color expression "IIf(RUNNINGVALUE(Fields!ROW_GROUP_COLUMN.Value,CountDistinct,Nothing) MOD 2, "White","#f6fb72")" and in Column Groups i used this expression =ReportItems!TXT_Color.Value. It is working perfectly.

    Please don't forget to hide dummy column.


    Thanks Shiven:)
    • Marked as answer by S Kumar Dubey Tuesday, May 04, 2010 10:03 AM
    Tuesday, May 04, 2010 10:03 AM

All replies

  • Thursday, April 22, 2010 7:19 AM
  • I can't go for this.
    Thanks Shiven:)
    Thursday, April 22, 2010 7:40 AM
  • This i have already checked. Not useful
    Thanks Shiven:)
    Thursday, April 22, 2010 7:41 AM
  • Hi S Kumar Dubey,

    Your issue is obviously cased by the Scope of the RunningValue function,we can achieve this in Sql Server Reporting Service 2008 using your expression.But in Sql Server Reporting Service 2005,we should select every textbox to set its background color with the right Scope rather than the whole row.See this picture:

    1. Click the Red circle and press 'F4',Property window will be displayed in your right side

    2. Find out the BackgroundColor property,and then type in the expression =IIF(Runningvalue(Fields!Category.Value,CountDistinct,"matrix1") mod 2,"Red","Lime").

    Note:matrix1 is the name of your matrix

    3. Click the green circle and press 'F4',type in the expression in the backgroundcolor property =IIF(Runningvalue(Fields!Category.Value,CountDistinct,"matrix1_Year") mod 2,"Red","Lime")

    Note:matrix1_Year is the name of the column group.

    Below picture is produced by the above steps, you can set your color depending on requirement.

    If you have any question,please feel free to ask.
    Thanks,
    Challen Foo

     

    Tuesday, April 27, 2010 6:02 AM
    Moderator
  • Hi All,

    My problem got resolved.

    I used below Method:

    I created a dummy column out of Column group. There in all Text box for dummy column, i gave this alternate color expression "IIf(RUNNINGVALUE(Fields!ROW_GROUP_COLUMN.Value,CountDistinct,Nothing) MOD 2, "White","#f6fb72")" and in Column Groups i used this expression =ReportItems!TXT_Color.Value. It is working perfectly.

    Please don't forget to hide dummy column.


    Thanks Shiven:)
    • Marked as answer by S Kumar Dubey Tuesday, May 04, 2010 10:03 AM
    Tuesday, May 04, 2010 10:03 AM
  • Hi Challen Foo,

    your method is not working for me.


    Thanks Shiven:)
    Tuesday, May 04, 2010 11:23 AM
  • Hi S Kumar Dubey,

    I am glad to hear that you solved the issue by yourself, no matter whose method,solving the problem is the top priority.Welcome to pose the case in this forum when you have questions, we are happy to discuss together.

    Regards,

    Challen Foo

    Wednesday, May 05, 2010 1:27 AM
    Moderator
  • Hi,

    We can use any of below expressions for Alternate row coloring

    =Iif(RowNumber(Nothing) Mod 2, "PaleGreen", "White")

    OR

    =Iif(RowNumber("Employees") Mod 2, "PaleGreen", "White")

    Where Employees is dataset Name

    OR

    "IIf(RUNNINGVALUE(Fields!ROW_GROUP_COLUMN.Value,CountDistinct,Nothing) MOD 2, "White","#f6fb72")"


    Thanks Shiven:)
    Wednesday, November 03, 2010 6:37 AM
  • My problem got solved from above answer.Thanks Challen Fu.

    Friday, December 03, 2010 1:09 PM
  • Excellent post... this solved my issue.  Tried the others above but I have some grouped columns coming back null or nothing and that screws with the MOD and thus color.  But creating a dummy column with the color value of the row is brilliant!  Great hack for inheriting from the parent :)
    Thursday, December 01, 2011 9:21 PM
  • Can you provide step by step  detail on how to set up the dummy column and what and where the formulas should be for the alternating color background?

    I cant work it out. I created a dummy column group called matrix1_dummy. I made the value = "Gainsboro". So how do I reference this in the background expression for value cell? Please provide more detail asap as its driving me crazy.

    Thanks

    Saturday, July 14, 2012 2:05 AM
  • Hi Smisich

    Created a dummy column (Extra Column) out of Column group. Give this alternate color expression "IIf(RUNNINGVALUE(Fields!ROW_GROUP_COLUMN.Value,CountDistinct,Nothing) MOD 2, "White","#f6fb72")" in TEXT BOX dummy column (Extra Column) of  and in Column Groups use this expression =ReportItems!TXT_Color.Value. It is working perfectly.

    Please Hide the dummy column (Extra Column) & It will not apear in reporting preview


    Thanks Shiven:) If Answer is Helpful, Please Vote

    Tuesday, July 17, 2012 1:14 AM
  • This solution worked perfectly for me too, until we recently migrated the report to a 2012 report server. (The report itself was not upgraded, it is 2008R2.)  The alternating group colors still works perfectly (blue, white, blue, white, blue, etc.) while browsing the report on the 2012 report server, but when exported to Excel (xlsx) it alternates only once (blue, white, white, white, white, etc.).  When exported to PDF, it alternates correctly.  I'm guessing the problem is Excel, not SSRS, but I'm wondering if anyone else has encountered this and if you've found another way to make the alternating group colors work exporting to Excel from an SSRS 2012 server?
    Thursday, February 07, 2013 5:59 PM