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

Monday, March 15, 2010 2:39 PM

• 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 Tuesday, March 23, 2010 6:01 AM
Friday, March 19, 2010 3:31 AM
• 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)
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 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.
Dasari
Monday, 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
• 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
• 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 Tuesday, March 23, 2010 6:01 AM
Friday, March 19, 2010 3:31 AM
• 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)
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 Friday, March 19, 2010 9:40 AM
Friday, March 19, 2010 9:39 AM