Nested Aggregates is not working

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

 

Answer 1

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

Answer 2

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.

Answer 3

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)

Answer 4

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

 

Answer 5

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

Answer 6

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.

Answer 7

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.