Nested Aggregates is not working RRS feed

  • Question

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




    Wednesday, December 21, 2011 10:06 PM

All replies

  • 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
    Thursday, December 22, 2011 1:32 PM
  • 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


    Thanks, Sandip Please "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.
    Thursday, December 22, 2011 3:22 PM
  • 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)

    Thursday, December 22, 2011 3:53 PM
  • 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.




    Thursday, December 22, 2011 5:22 PM
  • Hello Esmri,


       You need to use a custom code to achieve the same. Please have a look into these links









    Thursday, December 22, 2011 6:32 PM
  • 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.

    Friday, December 23, 2011 12:23 AM
  • 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:



    Please remember to mark the replies as answers if they help.
    • Marked as answer by Lola Wang Monday, January 2, 2012 8:03 AM
    • Unmarked as answer by Esmari Tuesday, January 10, 2012 4:18 AM
    Friday, December 23, 2011 3:56 AM