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.

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

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.

I think you need to do something like, running sum and performing average. Thanks KumarKG, MCTS

Thank you Kumar but I am not sure if I follow you.

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

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

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

Thank you I will try and let you know. Thank you again

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, JasonJason Long

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

Thank you for your reply. I appreciate the help.