Conditionally format subtotal for matrix report


  • I have a matrix that will pull out the current quarters sales figures. I want to change the color of the subtotal font ONLY when we are in the current period. I have a boolean field in the matrix report that is true when it is the current month.

    For example, at the end of last month it displays January, February and March figures. I want the sub total to display the totals for January and February in white, whilst the totals for March are Yellow.

    Any ideas anyone?

    Monday, April 03, 2006 2:06 PM

All replies

  • You can use expression for the subtotal style. Just check the value of your bool field in the expression, and return the corresponding color.
    Tuesday, April 04, 2006 3:04 AM
  • Unfortunately, that doesn't work.

    I am using a condition in the color field of the subtotal properties checking the boolean value - and it ALWAYS thinks that the value is true, whether it is or not. Problem is that I have a mixture in my matrix - the previous 2 months hold false, the current month true and I am displaying all 3 months.

    I am using the following in the color field in the properties of the subtotal:

    =IIf(Fields!CurrentPeriod.Value, "Yellow", "White")

    And it is showing yellow for all months.

    Any pointers greatfully received

    Tuesday, April 04, 2006 7:59 AM
  • Hi there,

    Did you find an answer to this? I am stuck with the same problem.

    -Thanks a lot
    Sunday, October 15, 2006 10:54 PM

                       Hi Ragas, Just try like this add your expression in differen place.Click in textbox and go to properties window,go to back ground color in that drop down list add your expression.


    Monday, October 16, 2006 10:52 AM
  • Hi folks,


              I have tried various methods for conditional formating in subtotal. Everything is working in development environment, but when i deploy and see the report in the http://ReportServer/reports, it is not being implemented. But when i print from the report from report server, i can see the conditional format !!!!

              Can anyone give solution for this?




    Monday, May 14, 2007 8:31 AM
  • Hi there,

    This worked for me:


    =iif(inscope("Rating_Group") and inscope("RowRating_Group"),"white","Gainsboro")


    Use in any of the attributes (Font, Color, Borderstyl) of the Data cell, in other words the cells that will contain the values of the matrix.


    "Rating_Group" and "RowRating_Group" simply represent the Group which the subtotal belongs to. I had two subtotals on my report that's why I used both.

    This can be a column group or a row group.

    You should be able to format the subtotals to your heart's content with this beaut.




    • Proposed as answer by ReportNewb Friday, June 12, 2009 6:20 PM
    Friday, June 15, 2007 1:19 PM
  • To All:

        I have a similar issue and have tried many things from posts at this site and others. 

    I have a very simple matrix report.  There is only one row group (BrokerID is the name) and one column group (AuditPeriod is the name).  There is only one subtotal - for the column group - heading "Total".

    What I am trying to accomplish is the changing of the color based on the range of the total.  If the total is one or less then the color is black, between 1 and 4 - Blue, over 4 is Red.  I have written a custom function for this and it works fine in the data cells. 

    What I have done so far is to place code in the expression for the Color property of the Subtotal and it returns red for every cell in the Total column.  What I have discovered is that any expressions for this Subtotal column appears to be fired once.  I put a msgbox() in the Subtotal expression.  I have tried various versions of InScope and have not been able to get anything to work.  I have tried the code that Ian posted and was not able to get that to work.  I was not quite sure where ot add the new column and so I tried adding it in a couple of places (row level and adjacent to the current column group).  Addmittedly this IS an implementation issue.  I have a lot more experience with Tables.  Any suggestions?


    Not sure if this will be helpful but here is a layout of the matrix


    BrokerID                                =Fields(AuditPeriod)                           Total

    =Fields(Broker.Value)          =Sum(Fields(AuditCount.Value)       <Grayed Out>


    Wednesday, November 07, 2007 2:37 PM