hi! i just trying to put the sum value of my column SUM(Fields!Column2.Value) in a calculated field but it failed. i want to reuse this Sumvalue in another report that is why i need to put it in a variable. i tried so: CalFi = SUM(Fields!Sales.Value) CalFi = ReportItems("Textbox13").Value but it didn't work? How can i put my Sumvalue in a field??? Thanks for helping! Newfon

Hi, When you say that the sum failed, what error message was displayed? I take it the other report is a Sub-Report? Regards Gary

1) Go to design mode and right click on the area outside the report body. Select Report Properties 2) Select the Code tab and paste the following code there Dim public totalBalance as Integer Public Function AddTotal(ByVal balance AS Integer ) AS Integer totalBalance = totalBalance + balance return balance End Function Public Function GetTotal() return totalBalance End Function The AddTotal function basically adds all the values of a textbox and sets it to totalBalance variable. The GetTotal function returns the sum of the values by the count of the values which is basically the average of the textbox values. Once you have pasted the code, you can click OK. 3) Replace the expression at the Total level from SUM(Fields!Sales.Value) to =Code.AddTotal(SUM(Fields!Sales.Value) ) 4) Now you can use the below code to get the sum of SUM(Fields!Sales.Value) =Code.GetTotal() You can try reading the article to check if it answers your question http://road-blogs.blogspot.com/2010/07/aggregate-of-aggregate-function-in-ssrs.html Cheers, Jason P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

Hi, If Sales is a calculated field try expression =SUM(CDbl(Fields!Sales.Value )). Best regards, Andrei

Hi Newfon, In case you want to reuse the value in another report, then you will have to pass it as a parameter to the other report (Navigation properties --> Jump to report). And then, you can pass the Code.GetTotal() to the parameter. But ideally, if you just want to pass the Sum(Fields!Calc.Value) and not the Sum(Sum(Fields!Calc.Value)), you could directly use it without the need for the code I gaveCheers, Jason P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

hi jason, thanks for your wonderful article! I think i will need it soon! But now my problem is i want to reuse my total value in another sub report. So I don't know how to call the calculated aggregate values from my actual report in the other report. (the 2 reports have the same dataset) Gary i think i answer your question also :) Newfon

hi, i didn't read the above suggestions! I will test your propositions now ans tell you! thanks! Newfon

Hi Jason, Please can you explain how to pass a value as a parameter to the other report? I have never done Thanks! Newfon

This link should help you http://msdn.microsoft.com/en-us/library/aa964132(SQL.90).aspxCheers, Jason P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

Hi Newfon, The Sum function cannot be used in calculated field expression. In order to pass the sum value to the subreport, we can add a parameter to the subreport, and then specify the parameter value to the expression like =Sum(Fields!Sales.Value) For the detailed steps to specify parameters to pass to a subreport, please read the article How to: Add a Subreport and Parameters at http://msdn.microsoft.com/en-us/library/ms160348(v=SQL.100).aspx Thanks, Tony ChainPlease remember to mark the replies as answers if they help and unmark them if they provide no help