locked
Sum of the averages. How do I do it? RRS feed

  • Question

  • I have a matrix that calculates averages but I need to add a total of the averages at the end of the matrix. Now if I use =sum(avg(FieldName!.Value), it does not work since SSRS does not support aggregate of aggregate. Please help how can I sum the averages. My matrix looks like below

     

    Product Type Interest Income Fees Earned Average Balance
    Auto $105.45 $172.06 $561.32
    Residential $2,706,305.07 $29,996.91 $406,759.58
    Commercial $723,965.83 $184.85 $1,363,393.48
    Total $3,430,376.35 $30,353.82 xxxxxxxxxxxxxxx
     

     

    I need the sum of the average balance where the xxxx is. The amount seen in the average balance column are the averages.

    Wednesday, December 22, 2010 8:00 PM

Answers

  • Hi YPendy,

    Nested aggregation is only supported in Reporting Services 2008 R2. As a workaround in Reporting Services 2008, we can use Custom Code to meet your needs.

    Generally, cells in the matrix are evaluated in a particular order. For example, in the Average Balance column, the value $561.32 is evaluated at first, then the value $406,759.58, $1,363,393.48 and the Total xxxx at last. So, we can store values for each Product Type in the custom code at first, then calculate and get the sum in the Total row at the last.  Please refer to the steps below:

    1.       In the main menu, click Report and select Report Properties.

    2.       In the Report Properties window, select Code in the left list.

    3.       Type in the code below:

     

    dim sumAvg as decimal=0

     

    public function addAvg(avg as integer) as decimal

                sumAvg=sumAvg+avg

                return avg

    end function

     

    public function getSumAvg() as decimal

                return sumAvg

    end function

     

    In the Custom code, the function addAvg is used to add each average to the variable sumAvg, and the function getSumAvg is used to return the sumAvg.

    4.       Call the addAvg function in the detail cell of Average Balance column.
    For example, if the original expression in the cell is =Fields!Average.Value, then change it to =Code.addSum(Fields!Average.Value)

    5.       Call the getAvgSum function in the total row by the expression =Code.getAvgSum()

    For more information about Custom Code, please refer to:
    http://msdn.microsoft.com/en-us/library/ms155798(v=SQL.100).aspx

     

    Thanks,
    Tony Chain


    Tony Chain [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Marked as answer by Tony Chain Thursday, December 30, 2010 1:56 AM
    Sunday, December 26, 2010 9:29 AM

All replies

  • hello, one way is to include the average balance via a subquery of each product within the query:

     

    select product, interest, fees, (select avg(balance) from temp where product = t.product) average_balance from temp t

    And then in the report simply sum up that column

    • Proposed as answer by gk1393 Wednesday, December 22, 2010 8:26 PM
    • Unproposed as answer by YPandey Wednesday, December 22, 2010 9:16 PM
    Wednesday, December 22, 2010 8:06 PM
  • Thank you very much for the reply but modifying the query would take a long time just because the way the query is structured. Is there another way I can do in the SSRS or BIDS? May be user defined code or something. Thank you.

    Wednesday, December 22, 2010 8:55 PM
  • I think you need to do something like,

    running sum and performing average.

    Thanks

    Kumar


    KG, MCTS
    Wednesday, December 22, 2010 8:57 PM
  • Thank you Kumar but I am not sure if I follow you.

    Thursday, December 23, 2010 3:43 PM
  • Hi YPendy,

    Nested aggregation is only supported in Reporting Services 2008 R2. As a workaround in Reporting Services 2008, we can use Custom Code to meet your needs.

    Generally, cells in the matrix are evaluated in a particular order. For example, in the Average Balance column, the value $561.32 is evaluated at first, then the value $406,759.58, $1,363,393.48 and the Total xxxx at last. So, we can store values for each Product Type in the custom code at first, then calculate and get the sum in the Total row at the last.  Please refer to the steps below:

    1.       In the main menu, click Report and select Report Properties.

    2.       In the Report Properties window, select Code in the left list.

    3.       Type in the code below:

     

    dim sumAvg as decimal=0

     

    public function addAvg(avg as integer) as decimal

                sumAvg=sumAvg+avg

                return avg

    end function

     

    public function getSumAvg() as decimal

                return sumAvg

    end function

     

    In the Custom code, the function addAvg is used to add each average to the variable sumAvg, and the function getSumAvg is used to return the sumAvg.

    4.       Call the addAvg function in the detail cell of Average Balance column.
    For example, if the original expression in the cell is =Fields!Average.Value, then change it to =Code.addSum(Fields!Average.Value)

    5.       Call the getAvgSum function in the total row by the expression =Code.getAvgSum()

    For more information about Custom Code, please refer to:
    http://msdn.microsoft.com/en-us/library/ms155798(v=SQL.100).aspx

     

    Thanks,
    Tony Chain


    Tony Chain [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Marked as answer by Tony Chain Thursday, December 30, 2010 1:56 AM
    Sunday, December 26, 2010 9:29 AM
  • Thank you I will try and let you know. Thank you again
    Tuesday, December 28, 2010 2:13 PM
  • Summing the averages won't give you valid values. You'll want to average the sums. To do that, you simply need to use the same average formula that you already have... Just copy it to the table footer (or next grouping level).

    HTH,

    Jason


    Jason Long
    Tuesday, December 28, 2010 5:02 PM
  •          
    Account Type Desc Balance Count Average  
    Mortgage $11,893,010.00 82 $145,036.71  
    Auto $10,799,409.59 548 $19,706.95  
    Miscellaneous Secured $324,427.42 43 $7,544.82  
    Home Equity $1,275,787.73 22 $57,990.35  
    Unsecured $331,239.37 84 $3,943.33  
    Share Secured $539,903.23 63 $8,569.89  
    Credit Card $274,341.10 72 $3,810.29  
    Motorcycle $94,495.78 9 $10,499.53  
    RV $49,015.46 3 $16,338.49  
    Share Secured Visa $3,771.27 12 $314.27  
    Boat $18,295.88 2 $9,147.94  
    Commercial Home Equity $42,000.00 2 $21,000.00  
    Platinum Credit Card $288,454.55 58 $4,973.35  
    Totals $25,934,151.38 1000 $25,934.15  
             
    Tuesday, December 28, 2010 5:32 PM
  • Thank you for your reply. I appreciate the help.

    Tuesday, December 28, 2010 6:30 PM
  • Tony, I have a similar problem, and your answer PARTIALLY solved my problem, however my table also has a column group, called Assessment. Your code will give me the added total for each column in that group, but I want a unique total for each column, if you need a reference please see my post at http://social.msdn.microsoft.com/Forums/sqlserver/en-US/1f9b19af-fed5-405d-bfd6-45e77fd02067/trouble-summing-grouped-values-in-report?forum=ssdt#e6a369df-9fae-4e64-8179-5af9d774ae68
    Thursday, March 27, 2014 6:42 PM