Answered by:
How to do SUM of Average in subtotal cell for the entire matrix
Question

Hi,
I have a matrix as shown below:
Head Count Jan07 Feb07 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 DivisionReturn 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 FunctionWednesday, May 2, 2007 4:48 AMAnswerer
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 DivisionReturn 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 FunctionWednesday, May 2, 2007 4:48 AMAnswerer 
Ian, would this code also help resolve my issue here?
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 PMAnswerer 
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.
IanFriday, August 17, 2007 9:35 PMAnswerer 
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.
IanMonday, September 10, 2007 11:35 PMAnswerer 
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.
IanOooh... 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.
IanTuesday, September 11, 2007 7:03 PMAnswerer 
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