tabbey


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




Re: How to do SUM of Average in subtotal cell for the entire matrix

sluggy


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.




Re: How to do SUM of Average in subtotal cell for the entire matrix

mverma

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







Re: How to do SUM of Average in subtotal cell for the entire matrix

tabbey

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





Re: How to do SUM of Average in subtotal cell for the entire matrix

Ian Roof - MSFT

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







Re: How to do SUM of Average in subtotal cell for the entire matrix

Rageheart

Ian, would this code also help resolve my issue here

http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=1515409&SiteID=1




Re: How to do SUM of Average in subtotal cell for the entire matrix

Ian Roof - MSFT

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.





Re: How to do SUM of Average in subtotal cell for the entire matrix

Susan22

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





Re: How to do SUM of Average in subtotal cell for the entire matrix

fwpi

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.





Re: How to do SUM of Average in subtotal cell for the entire matrix

VSempoux

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





Re: How to do SUM of Average in subtotal cell for the entire matrix

Ian Roof - MSFT

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






Re: How to do SUM of Average in subtotal cell for the entire matrix

PeterZ

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





Re: How to do SUM of Average in subtotal cell for the entire matrix

Ian Roof - MSFT

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





Re: How to do SUM of Average in subtotal cell for the entire matrix

PeterZ

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





Re: How to do SUM of Average in subtotal cell for the entire matrix

Ian Roof - MSFT

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