# How to do SUM of Average in subtotal cell for the entire matrix • ### 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

• 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

### 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
• 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
• 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
• 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
• 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
• 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.

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.

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 Tuesday, August 9, 2016 10:39 AM
Tuesday, August 9, 2016 10:37 AM