locked
How to do SUM of Average in subtotal cell for the entire matrix RRS feed

  • Question

  • Hi,

     

    I have a matrix as shown below:

     

    Head Count
    Jan-07 Feb-07 Average
    Dept1 59.00 62.00 60.50
    Dept2 21.00 21.00 21.00
    Total 80.00 83.00 81.50

     

    I am having trouble figuring out how to ADD the "Average" column to get the 81.50 (red). I tried SUM(AVG(Fields!....)) but it didn't work.

     

    Any help is appreacited!

     

     

    Thanks,

    Tabbey

    Thursday, April 19, 2007 3:24 PM

Answers

  • What you are looking to accomplish requires nested aggregates, the average of a subtotal. Nested aggregates are not currently supported. There is a workaround, however. The workaround is to use a custom function that uses the appropriate expression, based on the current scope. In this example, there are four different scopes in which the cell is calculated. One for the cell, the subtotal, average, and the average of the subtotals. The custom function for the first two cases simply returns the aggregate value passed in. The third case will take the subtotal passed in and add it to a running total, as well as keep a count values added to the total, and then return the subtotal. The fourth case will calculate and return the average, and reset the running total and count.

    The custom function is called using the following expression, assuming that the column group is named "Date" and the inner row grouping is named "Division."

    =Code.CalculateSumSubtotalOrAverage(Sum(Fields!FieldName.Value),AVG(Fields!FieldName.Value), InScope("Date"), InScope("Division"))

    Here is the custom function.


    Code Snippet


    Private m_total As Double
    Private m_count As Integer

    Public Function CalculateSumSubtotalOrAverage(subtotal As Double, average As Double, inDateScope as Boolean, inDivisionScope As Boolean) As Double

    If inDateScope And inDivisionScope Then
        ' Regular cell
        Return subtotal    


    Else If Not inDateScope And inDivisionScope Then
        ' Average of Division

        Return average


    Else If inDateScope And Not inDivisionScope Then
        ' Subtotal of Date
        m_total = m_total + subtotal
        m_count = m_count + 1

        Return subtotal

    Else
        ' Average of Subtotal
        Dim avg as Double
        avg =  m_total / m_count

        m_count = 0
        m_total = 0

        Return avg
        
    End If

    End Function


    Wednesday, May 2, 2007 4:48 AM
    Answerer

All replies

  • Don't try to SUM it, just leave the formula as is and it should give you the correct result in the total row, the average of 80 and 83 is 81.50.
    Thursday, April 19, 2007 11:47 PM
  • Can you explain me what you did in report ?

    How do you populate data into the Avg and Total i.e subtotal of rows and columns ?

    Friday, April 20, 2007 6:04 AM
  • Sluggy,

     

    I apologize I did not explain that correctly. The Dept has a nested group of Divisions as shown in the new example below. The  RED highlited cells give the "WRONG" AVERAGE (using function AVG). That's the reason I think I need the AVG(SUM(...)).

     

        Jan 07 Feb 07 Average
    Dept1 Div 1A 345.00 345.00 345.00
      Div 1B 41.00 41.00 41.00
      Div 1C 283.00 283.00 283.00
      Total 669.00 669.00 223.00
    Dept2 Div 2A 8.00 8.00 8.00
      Div 2B 63.00 63.00 63.00
      Div 2C 2.00 2.00 2.00
      Total 73.00 73.00 24.33

     

    As the result, I get the wrong average when the Divisions are collapsed as shown below (in RED highlite):

     

      Jan 07 Feb 07 Average
    Dept1 669.00 669.00 223.00
    Dept2 73.00 73.00 24.33

     

    What is the best solution for situation like this?

     

    Thanks,

    Tabbey

    Tuesday, April 24, 2007 2:17 PM
  • What you are looking to accomplish requires nested aggregates, the average of a subtotal. Nested aggregates are not currently supported. There is a workaround, however. The workaround is to use a custom function that uses the appropriate expression, based on the current scope. In this example, there are four different scopes in which the cell is calculated. One for the cell, the subtotal, average, and the average of the subtotals. The custom function for the first two cases simply returns the aggregate value passed in. The third case will take the subtotal passed in and add it to a running total, as well as keep a count values added to the total, and then return the subtotal. The fourth case will calculate and return the average, and reset the running total and count.

    The custom function is called using the following expression, assuming that the column group is named "Date" and the inner row grouping is named "Division."

    =Code.CalculateSumSubtotalOrAverage(Sum(Fields!FieldName.Value),AVG(Fields!FieldName.Value), InScope("Date"), InScope("Division"))

    Here is the custom function.


    Code Snippet


    Private m_total As Double
    Private m_count As Integer

    Public Function CalculateSumSubtotalOrAverage(subtotal As Double, average As Double, inDateScope as Boolean, inDivisionScope As Boolean) As Double

    If inDateScope And inDivisionScope Then
        ' Regular cell
        Return subtotal    


    Else If Not inDateScope And inDivisionScope Then
        ' Average of Division

        Return average


    Else If inDateScope And Not inDivisionScope Then
        ' Subtotal of Date
        m_total = m_total + subtotal
        m_count = m_count + 1

        Return subtotal

    Else
        ' Average of Subtotal
        Dim avg as Double
        avg =  m_total / m_count

        m_count = 0
        m_total = 0

        Return avg
        
    End If

    End Function


    Wednesday, May 2, 2007 4:48 AM
    Answerer
  • Ian, would this code also help resolve my issue here?

     

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1515409&SiteID=1
    Wednesday, May 2, 2007 8:51 PM
  • Yes, it should, but you will need to pass in the results of the InScope function for Year and Quarter, and the only the result of the SUM aggregate are needed.

    In the function, if Year is in scope and Quarter is not, then update the variance calculation, and return the subtotal. If both Year and Quarter are not in scope, then calculate and return the variance. Otherwise, just return the sum. This should produce the variance over the year subtotals for the column grand total and regular subtotals for the Customer and Year subtotals.
    Wednesday, May 2, 2007 9:53 PM
    Answerer
  • Hi Ian,

     

    I don't know visual basic and I don't know how to calculate the variance using a visual basic code. Could you write a visual basic code that calculates the variance in scope just like the one you wrote above?

    Thanks, Susan

     

    Wednesday, May 16, 2007 11:54 PM
  • Ian,

    I have a report project in VS2005 with a matrix on the report.  I have added a rowgroup on date and I want to average the values for each column at the bottom of the report.  I cannot find anything within the Matrix Properties dialog that lets me specify Averaging as opposed to Summing the totals.

     

    I see what you are doing with this function, but where do I put this code?  VS will not let me add a code module or anything other than another report.

     

    Thanks,

    Russ.

     

     

    Thursday, August 16, 2007 5:31 PM
  •  

    Hi Ian,

     

    Could your code be adapted to give me the max value of a specific column of a table ?

    For more details please check out this : http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1979307&SiteID=1

     

    If you could help me with some hints this would be great.

     

    Greetings

    Vinnie

    Friday, August 17, 2007 8:21 AM
  • Hi Vinnie,

    Yes, the above can be modified to accomplish this. However, you don't really need anything this complicated.

    You can use the first function below in the body of the table, and use the second one in the table footer.


    Code Snippet

    Private m_max As Integer = -1

    Public Function StoreMaxValueForHour(maxValueForHour as Integer) As Integer

    m_max = Math.Max(m_max, maxValueForHour)
    Return maxValueForHour

    End Function


    Public Function GetMaxHourSubtotal() As Integer

    Return m_max

    End Function

    The first one is called like this:

    =Code.StoreMaxValueForHour(CInt(math.Max((sum(Fields!CountTest1.Value)/sum(Fields!CountTotalTest.Value)*100), math.Max((sum(Fields!CountTest2.Value)/sum(Fields!CountTotalTest.Value)*100), math.Max((sum(Fields!CountTest3.Value)/sum(Fields!CountTotalTest.Value)*100), math.Max((sum(Fields!CountTest4.Value)/sum(Fields!CountTotalTest.Value)*100), (sum(Fields!CountTest5.Value)/sum(Fields!CountTotalTest.Value)*100)))))))

    Actually, you can simplifiy this a little using ReportItem references:

    =Code.StoreMaxValueForHour(math.Max(ReportItems!TableBodyCountTest1.Value, math.Max(ReportItems!TableBodyCountTest2.Value, math.Max(ReportItems!TableBodyCountTest3.Value, math.Max(ReportItems!TableBodyCountTest4.Value, ReportItems!TableBodyCountTest5.Value)))))

    The second in the footer is called like:

    =Code.GetMaxHourSubtotal()

    I hope this helps.

    Ian

    Friday, August 17, 2007 9:35 PM
    Answerer
  • I've been reading this discussion thread as I'm running into a similar problem wherein I have a table that performs a Count(# of support cases) per Month. The table groups on month. I'm trying to get the max(count(#support cases)).

     

    I tried to implement the code suggested from above; the first function works fine "StoreMaxValueForHour", but the second function just returns -1. I've placed the second function in the table footer.

     

    What's the secret to making it work?

     

    Monday, September 10, 2007 4:09 PM
  • The reason this is not working is that the Table headers and footers are calculated before the inner groups and details. The code discussed here is really only appropriate for Matrix report items.

    Ian
    Monday, September 10, 2007 11:35 PM
    Answerer
  •  Ian Roof - MSFT wrote:
    The reason this is not working is that the Table headers and footers are calculated before the inner groups and details. The code discussed here is really only appropriate for Matrix report items.

    Ian

     

    Oooh... good to know. Well, okay then.

     

    Thanks for the clarification, Ian.

     

    So, I'm guessing this means that a table data region just doesn't offer much in the way of getting to a solution for an aggregate of an aggregate. Your suggestion (relevant to a matrix) seems like the closest thing to a simple solution of a nested aggregate that I've been able to find.

     

    --Pete

    Tuesday, September 11, 2007 12:28 PM
  • One thing you may be able to try is to create a two Tables. One table that is hidden and uses the first function that does not have headers and footers, and your current table that only uses the second function. Make sure that the new, hidden table is above and to the left of your current table, so that it will be executed first.

    Ian
    Tuesday, September 11, 2007 7:03 PM
    Answerer
  •  

    Pl give me a query how to add column for grand total
    Tuesday, October 30, 2007 6:13 AM
  • Hi i've browsed almost all posible links regarding subtotals in matrix, im a newbie in using SSRS especially with matrix.

    i need to show only the subtotal of the CURRENT column, but if im using the subtotal function in the grouping it applies to all columns, is it posible to hide the subtotals or to disable the subtotal in other columns so that only the CURRENT column will be visible in the report.

     

    thanks! i would appreciate any comments and ideas from you guys! GodBless!

    *current=sum(fields!column_a.value)*fields!column_b

     

                                                                                  COLUMN_A            COLUMN_B                 CURRENT

     DEPT        per subdept         subsubgroup                       2                            3                                6

     A                  sample1            sample1                           4                             5                               20

     B

    subtotal                                                             subtotal of column_a | subtotal of column_b | subtotal of current

     

    grand total:

     

     

    i only need to show the subtotal of the current column and not all the columns.

     

    i cant paste the screenshot of my report. please help me. thanks... if you have any comments or inputs about my problem please email me at marirose_figueroa@hotmail.com or rose_animo@yahoo.com

     

    thanks a lot!

    Tuesday, March 4, 2008 8:03 AM
  • Hi i've browsed almost all posible links regarding subtotals in matrix, im a newbie in using SSRS especially with matrix.

    i need to show only the subtotal of the CURRENT column, but if im using the subtotal function in the grouping it applies to all columns, is it posible to hide the subtotals or to disable the subtotal in other columns so that only the CURRENT column will be visible in the report.

     

    thanks! i would appreciate any comments and ideas from you guys! GodBless!

    *current=sum(fields!column_a.value)*fields!column_b

     

                                                                                 

    DEPT | per subdept | subsubgroup | COLUMN_A | COLUMN_B | CURRENT

     A | sample1 | sample1 | 4 | 5 | 20

     B | sample2 | sample2 | 2 | 3 | 6

    subtotal | - | - | subtotal of column_a | subtotal of column_b | subtotal of current

     

    grand total:

     

     

    i only need to show the subtotal of the current column and not all the columns.

     

    i cant paste the screenshot of my report. please help me. thanks... if you have any comments or inputs about my problem please email me at marirose_figueroa@hotmail.com or rose_animo@yahoo.com

     

    thanks a lot!

    Tuesday, March 4, 2008 8:08 AM
  • I'm trying to do something similar, but my calculation needs to be for each date column and only averages together eligible subtotals, which I have as a flag in the data.  The percentages shown are all calculations done on the matrix and I'm using SSRS 2005.  I can get the Subtotals working just fine, even the % all correct, but I cannot get the Overall Test Scores in the columns.  I need a flat average of the Section 1, Section 2 and Section 3 subtotals - which are created on the report as sum(positive)/sum(possible) because my Date fields can either group by month or quarter.  I've tried Ian's code, but I believe that I have the scoping wrong, as I can use it for the detail and subtotals by not the overall column totals.  I've even tried the Overall score as a second subtotal and couldn't get it to work - I get Nan returned because the m_count  is equal to 0.  Here is what the matrix should look like, I'm fine with the Overall Test score being in the column header or in a grandtotal field at the top of the report. 
      2010
      Jan Feb
    Overall Test Score 84% 85%
    Section 1 Subtotal 91% 90%
    Q1 89% 88%
    Q2 85% 86%
    Q3 98% 97%
    Section 2 Subtotal (% all correct) 77% 79%
    Q4 85% 83%
    Q5 98% 99%
    Section 3 Subtotal 83% 85%
    Q6 80% 82%
    Q7 85% 87%
    Section 4 Subtotal (not included in total) 22% 30%
    Q8 22% 30%
    Thursday, March 11, 2010 9:52 PM
  • Can you show me how you created this matrix table(design view?) I have a similar task.  Can you show your final result?
    Wednesday, July 28, 2010 8:03 PM
  • Hi,

    Need a help in Matrix report in SSRS. We have two row group and one column group and we using expression in data field. We want “Sub Total” and “Grand Total” of that expression.

    Below is the reports. Requester Name and Complexity Major is row group and Services is column group. We need sum of Sum/Count (193.5+781.48+147.64) in Pink Cell just besides the 143 for Java Services for all services.

    Requester

    Complexity

    Java Services

    ABC

     

    Sum of Hrs

    Count of Req

    Sum/Count

    Complex

    1935

    10

    193.50

    Medium

    4688.9

    6

    781.48

    Simple

    18749.95

    127

    147.64

    Total

    25373.85

    143

    DEF

     

    Sum of Hrs

    Count of Work

    Sum/Count

    Complex

    2515.5

    8

    314.44

    Medium

    598

    3

    199.33

    Simple

    17168

    99

    173.41

    Total

    20281.5

    110

     



    • Edited by techanurag Tuesday, August 9, 2016 10:39 AM
    Tuesday, August 9, 2016 10:37 AM