locked
nested aggregates RRS feed

  • Question

  • the first group is displays an average value aggregate. I'm trying to display the sum of these averages in an outer group (not the footer).  All that I could think of has failed to work so any help out be appreciated.  Thanks

    Friday, February 13, 2009 12:28 AM

Answers

  • Hi,


    Writing custom code is an optimal approach to getting the sum of the group average values. Open the report properties dialog box and add the following functions in the code tab.

    Dim myVal As Double 
     
    Public Function SetMyVal(ByVal temp As DoubleAs Double 
            myVal = myVal + temp  
            SetMyVal = temp  
    End Function 
     
    Public Function GetMyVal() As Double 
            GetMyVal = myVal  
    End Function   
     



    Then, use the following expression in the cell where you want to display the average value for the group:
    =Code.
    SetMyVal(avg(Fields!fieldname.value))

    To show the sum of the averages, add a textbox and set the expression as =Code.GetMyVal().


    If you have any more questions, please let me know.

    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Tuesday, February 17, 2009 8:08 AM

All replies

  • Sorry, specifying aggregates of aggregates is currently not supported.  We are hoping to add this in a future release.
    If possible, try to perform the calculation already in the query.

    Thanks,
    Robert
    Robert Bruckner   http://blogs.msdn.com/robertbruckner

    This posting is provided "AS IS" with no warranties, and confers no rights.

    Sunday, February 15, 2009 6:55 PM
  • Hi,


    Writing custom code is an optimal approach to getting the sum of the group average values. Open the report properties dialog box and add the following functions in the code tab.

    Dim myVal As Double 
     
    Public Function SetMyVal(ByVal temp As DoubleAs Double 
            myVal = myVal + temp  
            SetMyVal = temp  
    End Function 
     
    Public Function GetMyVal() As Double 
            GetMyVal = myVal  
    End Function   
     



    Then, use the following expression in the cell where you want to display the average value for the group:
    =Code.
    SetMyVal(avg(Fields!fieldname.value))

    To show the sum of the averages, add a textbox and set the expression as =Code.GetMyVal().


    If you have any more questions, please let me know.

    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Tuesday, February 17, 2009 8:08 AM
  • thank you
    that seems like it could work but when I use the GetMyVal() function, it always displays 0
    Tuesday, February 17, 2009 10:46 PM
  • Hi,


    Does it get the correct values when you use the SetMyVal method?  If it does work, please check the location of the textbox where you use the GetMyVal method. You need to put the textbox after the table or other data region control.



    If you have any more questions, please let me know.


    Thanks.

     


    ***Xiao Min Tan***Microsoft Online Community***
    Wednesday, February 18, 2009 2:13 AM
  • Ok thanks.  Unfortunately this method doesnt look like it will accomplish what I am attempting to do.  I'm just going to put the aggregate into the query.
    Wednesday, February 18, 2009 6:55 PM