none
Divide row by group subtotal

    Question

  • Back Again...
    Group, I am trying to figure out if there is a way to divide rows by a "group" total.  For example I have a report laid out like such (the percent column is what I am trying to add):

    Membership Length 1 Year             Count     %
                                             Active     - 1000     .50%
                                             Inactive   - 1000    .50%
                                             Total       - 2000     100%
    Membership Length 2 Years
                                             Active     - 2000     .66%
                                             Inactive   - 1000    .33%
                                             Total       - 3000    100%

    (Grand)Total                                                 5000

    As you can see I want a percent column, but I want a percent for the group total not the Total of all accounts (5000).
    Is there an expression for this? 

    The matrix1 groups are: 
    =Fields!MembershipLength.Value
    =Fields!Status.Value

    Again, not sure if this is clear but any help is greatly appreciated.
    Wednesday, April 29, 2009 6:40 PM

All replies

  • Hi,

    You can create a group total (aka subtotal) row by adding a row in the scope of the row group, and the expression in the cell textbox is the same as the expression in your grand total.  That works because an expression is evaluated in its scope (unless you explicitly pass in a different scope).  Its scope can be a group, a data region, or the entire dataset.  In the case of subtotal, you want the scope to be your row group.  The scope of your grand total row is the table report item (which is a data region).

    To calculate the %, you can try this expression in your table cell:
    =Count(Fields!MemberID.Value) / Count(Fields!MemberID.Value, "MembershipLength")

    (assuming the unique key field in your dataset is "MemberID", and the name of your outer row group is "MembershipLength".)



    For SSRS 2005, lesson 5 of this tutorial talks about how to add a subtotal row to a table:
    http://msdn.microsoft.com/en-us/library/ms170623(SQL.90).aspx

    For SSRS 2008:
    http://msdn.microsoft.com/en-us/library/dd239342.aspx

    Hope this helps!


    Cheers,
    Lawrence
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, April 30, 2009 8:03 AM
    Answerer
  • Hello,

    Try this expression in the percentage cell

    =IIF(SUM(Fields!Count.Value) > 0 , SUM(Fields!Count.Value)/SUM(Fields!Count.Value,"GroupActiveName"),0)

    And for this cell the format should be percentage

    Hope its clear and helpful...

    Pavan http://www.ggktech.com
    Thursday, April 30, 2009 10:07 AM
  • This is a matrix, and I am getting the subtotals for each group (1 year, 2 year, etc.) by right clicking on that group and selecting subtotal.  SSRS calculates that so technically it is not its own "group."

    I did the count of MembershipID in my original SQL query, so I am using SUM.  Here is my expression:
    =Sum(Fields!ZO.Value)/Sum(Fields!ZO.Value,"matrix1_Status")

    The outermost group is matrix1_MembershipLength and the next level is matrix1_Status
    Thursday, April 30, 2009 1:13 PM
  • Ok, I also forgot to mention that I have two column groups for Customer Types.  When I use
    /Sum(Fields!ZO.Value,"matrix1_MembershipLength") this calculates a subtotal for each status group but the subtotal is for both customer types.  I need to divide by the subtotal for each customer type not both.

    How can I get /Sum(Fields!ZO.Value,"matrix1_MembershipLength") to equal the subtotal for the corresponding customer type membership length total?
    Thursday, April 30, 2009 2:06 PM
  • It sounds like you don't use dynamic column groups, but rather a static column for the customer types.  As a result, you have two column cells in the matrix body layout, correct?

    You might want to try this expression for the active customer category (and similarly for the other category):
    =Sum(Fields!ZO.Value) / Sum(iif(Fields!Status.Value = "Active", CInt(Fields!ZO.Value), 0), "matrix1_MembershipLength")

    HTH,
    Robert
    Robert Bruckner   http://blogs.msdn.com/robertbruckner
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, April 30, 2009 3:23 PM
    Owner
  • Not 100% sure I follow, but the Customer Type columns are generated by a CustomerType data set.  That is the column group for my matrix.

    Basically I have two row groups: MembershipLength then a sub group for Status.  Then I have the CustomerType column groups.  See below.  The % of total is what I am working on.


                                                      Retail Accounts                  Commercial Accounts
    1 Year Account
                          Active              Count | % of Group total            Count | % of Group Total
                          Inactive           Count | % of Group total             Count | % of Group Total
                          Group Total      Sum of rows | 100%                   Sum of rows | 100% 

    2 Year Account
                          Active              Count | % of Group total            Count | % of Group Total
                          Inactive           Count | % of Group total             Count | % of Group Total
                          Group Total      Sum of rows | 100%                   Sum of rows | 100% 
    Etc.

    Even if I break it down and in the % column just use =Sum(Fields!ZO.Value,"matrix1_MembershipLength")  which I would latter divide by to get the % I get the Sum of Group Totals for Retail AND Commercial.  I just want the corresponding customer type.  For example if there are 5000 Retail and 6000 Commercial the previous expression gives me 11000 in the % column when I really only want the total for that customer type...
    • Edited by tous0024 Thursday, April 30, 2009 3:49 PM edit
    Thursday, April 30, 2009 3:46 PM