Answered by:
Sum of averages
Question

Hi,
I'm trying to sum averages of values in a matrix.
http://img139.yfrog.com/img139/5391/usmatrix.jpg
In this pic the values are average for a product/customer and year. When I want to calculate the sum of a product, I get the sum of the details lines (52 for example) instead of the sum of the averages values (15+22)
I know how to use custom code with a table but it doesn't work with a matrix (I've tried to use a structure to store values for each year and each product).
Thanks in advance for your help.Monday, March 15, 2010 2:39 PM
Answers

Hi,
Custom Code can meet your requirement. As the example you provide, you may try these codes:
Public Shared x as Integer=0 Public Shared y as Integer=0 Public Function SumAvg(Value as Integer, Year as Integer) as Integer If Year=2009 Then x=x+value Else y=y+value End If Return Value End Function Public Function RetureSum(Year as Integer) as Integer If Year=2009 Then Return x Else Return y End If End Function
Hopefully this helps.
Yao Jie Tang Microsoft Online Community Marked as answer by Tony Tang_YJ Tuesday, March 23, 2010 6:01 AM
Friday, March 19, 2010 3:31 AM 
Thanks a lot for your reply.
Actually I need to store average for each year and each product.
The problem is that I've called SumAvg in a variable at the customer rows group and the result wasn't ok.
I've just moved the call of the function in the average cell in the matrix and now it's ok.
I've used a structure to store each product and each year dynamically.
Here is the code :
Public Class OneCell Public year As Integer Public product As Integer Public value As Integer Public Sub New(ByVal y As Integer, ByVal p As Integer, ByVal v As Integer) year = y product = p value = v End Sub End Class Public Class MyStructure Public listOfValues As System.Collections.ArrayList Public Sub New() listOfValues = New System.Collections.ArrayList End Sub Public Function getValue(ByVal y As Integer, ByVal p As Integer) As Integer For Each c As OneCell In listOfValues If c.year = y And c.product = p Then Return c.value End If Next Return 1 End Function Public Sub setValue(ByVal y As Integer, ByVal p As Integer, ByVal v As Integer) Dim found As Boolean = False For Each c As OneCell In listOfValues If c.year = y And c.product = p Then found = True c.value = c.value + v End If Next If found = False Then Dim c As OneCell = New OneCell(y, p, v) listOfValues.Add(c) End If End Sub End Class Dim _list as MyStructure=new MyStructure() Public Function SumAvg(Value as Integer, Year as Integer,Product as Integer) _list.setValue(Year,product,value) End Function Public Function RetureSum(Year as Integer,Product as Integer) as Integer Return _list.getValue(Year,Product) End Function
 Marked as answer by SkYneT54 Friday, March 19, 2010 9:40 AM
Friday, March 19, 2010 9:39 AM
All replies

try to check your grouping and are you using SSRS 2005 or 2008??
I reacan it's a problem in grouping.
DasariMonday, March 15, 2010 11:53 PM 
try to check your grouping and are you using SSRS 2005 or 2008??
I reacan it's a problem in grouping.
Dasari
This is indeed a grouping issue. Make sure that you add the SUM in the product group. The SUM will calculate everything in that grouping. You can also use a scope. If you use an aggregate function, like SUM you can force a scope yourself. Like this: SUM(fields!productsales.value,"Table1") Table1 is the name of the table, you can also use a grouping or a dataset instead of a table. So you probably want to make it SUM(fields!productsales.value,"Productgroup"). Make sure you actually add the " ", this is required.
This ofcourse can also be achieved by fixing you groups and putting the sum in the right group.Tuesday, March 16, 2010 8:25 AM 
Thanks for your reply.
I'm using SSRS 2008.
I've already tried to add the scope in the sum but it sums details values for all the years of a product.
Here is my report in design mode :
http://img13.imageshack.us/img13/5259/groupvf.jpg
Here is the result:
http://img638.imageshack.us/img638/1521/resultoo.jpg
I don't think it's possible to do this so easily ( I hope I'm wrong :p)Tuesday, March 16, 2010 9:02 AM 
Instead of SUM(Fields!Value.Value) try AVG(Fields!Value.Value).
The grouping looks fine. You just want AVG(SUM()) but that's not possible, or not neccesary. AVG should do the trick.Tuesday, March 16, 2010 9:46 AM 
Thanks for your reply but actually I want sum(avg()) If I replace sum(Fields!Value.Value) with avg(Fields!Value.Value), I'll get the average of all the details lines for each product.
For example, for product 1 in 2009 I'll get 17.33 since I've 2 rows with 15 and 1 with 22 ((15+15+22)/3=17.33) instead of 15+22.
Tuesday, March 16, 2010 10:05 AM 
If you indeed want the sum of the averages per grouping then you're out of luck. SUM(AVG()), or any double aggregations, is a feature that will be enabled in SQL server 2008 R2(Report builder 3.0) and is currently not possible.
From MSDN:
Calculating Aggregates of Aggregates
You can create expressions that calculate an aggregate of an aggregate. For example, in a cell in the row group header that is associated with a group based on year, you can calculate the average monthly sales for year by using the expression
=Avg(Sum(Fields!Sales.Value,"Month"),"Year")
.By using this feature for charts and gauges that are nested in a table, you can align horizontal and vertical axes for charts and scales for gauges. You do this by calculating the maximum and minimum of aggregated values so the nested items use the same ranges.
Tuesday, March 16, 2010 10:35 AM 
I know that it's not possible in 2008 but I hoped that someone had found a workaround.
I've tried to create a VB.Net structure to store the average of year and product that I populated with a call to a VB function in each average cells. Then I replaced the sum(Fields!Value.Value) with Code.myFunction(Fields!Year.Value,Fields!Product.Value) but it works only for 2009.Tuesday, March 16, 2010 11:31 AM 
If there is no solution I'll try to edit my query to add a new column with the average.Thursday, March 18, 2010 8:37 AM

Hi,
Custom Code can meet your requirement. As the example you provide, you may try these codes:
Public Shared x as Integer=0 Public Shared y as Integer=0 Public Function SumAvg(Value as Integer, Year as Integer) as Integer If Year=2009 Then x=x+value Else y=y+value End If Return Value End Function Public Function RetureSum(Year as Integer) as Integer If Year=2009 Then Return x Else Return y End If End Function
Hopefully this helps.
Yao Jie Tang Microsoft Online Community Marked as answer by Tony Tang_YJ Tuesday, March 23, 2010 6:01 AM
Friday, March 19, 2010 3:31 AM 
Thanks a lot for your reply.
Actually I need to store average for each year and each product.
The problem is that I've called SumAvg in a variable at the customer rows group and the result wasn't ok.
I've just moved the call of the function in the average cell in the matrix and now it's ok.
I've used a structure to store each product and each year dynamically.
Here is the code :
Public Class OneCell Public year As Integer Public product As Integer Public value As Integer Public Sub New(ByVal y As Integer, ByVal p As Integer, ByVal v As Integer) year = y product = p value = v End Sub End Class Public Class MyStructure Public listOfValues As System.Collections.ArrayList Public Sub New() listOfValues = New System.Collections.ArrayList End Sub Public Function getValue(ByVal y As Integer, ByVal p As Integer) As Integer For Each c As OneCell In listOfValues If c.year = y And c.product = p Then Return c.value End If Next Return 1 End Function Public Sub setValue(ByVal y As Integer, ByVal p As Integer, ByVal v As Integer) Dim found As Boolean = False For Each c As OneCell In listOfValues If c.year = y And c.product = p Then found = True c.value = c.value + v End If Next If found = False Then Dim c As OneCell = New OneCell(y, p, v) listOfValues.Add(c) End If End Sub End Class Dim _list as MyStructure=new MyStructure() Public Function SumAvg(Value as Integer, Year as Integer,Product as Integer) _list.setValue(Year,product,value) End Function Public Function RetureSum(Year as Integer,Product as Integer) as Integer Return _list.getValue(Year,Product) End Function
 Marked as answer by SkYneT54 Friday, March 19, 2010 9:40 AM
Friday, March 19, 2010 9:39 AM