locked
Sum of averages RRS feed

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