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

    Thanks,

    Esmari

     

    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

    http://msdn.microsoft.com/en-us/library/ms170438.aspx#Aggregates


    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.

    Thanks,

    Esmari

     

    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

     

    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
    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:
    http://msdn.microsoft.com/en-us/library/ms189461.aspx

     

    Thanks,
    Lola


    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