Aggregate Functions Cannot be Nested Inside Other Aggregate Functions

Question

Hi,

I am getting the following error message when I try to deploy my report in SSRS.

The Value expression for the textrun 'Textbox3.Paragraphs[0].TextRuns[0]' contains an aggregate function (or RunningValue or RowNumber functions) in the argument to another aggregate function (or RunningValue).  Aggregate functions cannot be nested inside other aggregate functions.

the expression I am using is:

=(sum(IIF(Fields!Grouped.Value, Fields!Pay.Value,0)/(sum(Fields!Grouped.Value))))

Grouped is a count in SQL and Pay is numeric field which is just data.

I have tried lots of different methods but still getting the error message.

thanks 

Answer 1

Hi Haze,

As the error message said, your have aggregation function inside of another aggregation function (sum in another sum). In Reporting Services, nested aggregation is released since SSRS 2008 R2. You can also achieve it using custom code. Please see: Aggregate of an aggregate function in SSRS

Also see similar thread below:

https://social.msdn.microsoft.com/Forums/en-US/b6be86e8-66ab-4044-9b44-a666f254f176/aggregate-functions-cannot-be-nested-inside-other-aggregate-functions?forum=sqlreportingservices

http://prologika.com/CS/blogs/blog/archive/2009/11/11/aggregates-of-aggregates.aspx

Regards,

---

Simon Hou
TechNet Community Support

Answer 2

I think it may be issue with improper use of braces

Between I'm not able to make out much from your expression

The IIF condition seems to be a trivial one

do you mean this?

=sum(IIF(Fields!Grouped.Value, Fields!Pay.Value,0))/IIF(sum(Fields!Grouped.Value)=0,Nothing,sum(Fields!Grouped.Value))

---

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

Answer 3

Thank you.