Hi, We have a ColumnB which has the expression ColumnA/sum(ColumnA). Now i want the total for ColumnB i.e Sum( ColumnA/sum(ColumnA)). Now SSRS is throughing an error that nested aggregates cannot be used. I also tried adding a calculated field in the dataset with the expression : reportitems("textbox1").value and using this in the table. But its not working. Kindly help. Thanks, Esmari

Hi, Are you wondering for Total at the end of detailed row, I means total of all values from Column B. Or are you going for summation at detail level.Cheers!! Sumit

Esmari, If you are using SQL Server 2008 R2 Reporting services in this case you will be able to use Aggregates of Aggregates Please refer following link for your reference http://msdn.microsoft.com/en-us/library/ms170438.aspx#AggregatesThanks, Sandip Please "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

Before 2008 you cannot aggregate over aggregates. This being said, there may be some work arounds. Are you sure about your example? ... Because Sum(ColumnA/sum(ColumnA)), if it succeeded, would be equal to 1. If you want subtotals, then you can have the following formula everywhere in your column B (including subtotals) Sum(yourexpression) / Sum(yourexpression, yourtablix)

Hi LaurentC, We need the total to be 1... But since we do nested aggregates,its througing an error !! Kindly help me with some work arounds. Thanks, Esmari

Hello Esmri, You need to use a custom code to achieve the same. Please have a look into these links http://msdn.microsoft.com/en-us/library/ms155798.aspx http://social.msdn.microsoft.com/Forums/is/sqlreportingservices/thread/96543746-d327-467c-be23-2a154d3d797b hth, Ram Ram

Well, that is just the way the math is: Sum(columnA / Sum (columnA)) = Sum(columnA) / Sum (columnA) = 1 Always ... So even if you could aggregate the aggregate, it would not calculate anything else but Sum(columnA) / Sum (columnA) = 1. You might as well enter =1 as the formula. It will make your report faster for the same result.

Hi Esmari, I suggest you to create the calculated column B on SQL query level with OVER clause: select [ColumnA],1.0*[ColumnA]/Sum(ColumnA) over() as ColumnB From [TableName] More information about OVER Clause, please see: http://msdn.microsoft.com/en-us/library/ms189461.aspx Thanks, Lola Please remember to mark the replies as answers if they help.