**Tags**

Aggregate functions can be used only on report items contained in page headers and footers : Using Aggregate on Aggregate function in Expression, SSRS

Hello Everyone,

In my Previous Post I explained about the count of rows in a column containing values in it. My next task was to count the no of columns in a row containing values in it and finally summing up the total values. It was like finding the aggregate(sum) of the Aggregate function already used in the Expression.

Firstly I used the Sum function under Aggregate 1 to count the no of columns under each row. The result was as correct as expected. But since I had to find the Sum of the Aggregate 1, I used ReportItems to find the sum like:

=sum(reportitems!Textbox72.Value)

but it resulted in the following * error*:

I then Google out and found a very helpful post on msdn.

- According, I created a
under**Function**.**Reports Properties**

- Then under
, I used the Custom function which sums up the Total after each row. like:-**First Expression**(i.e. Aggregate 1)

=Code.Sum(IIF(IsNothing(Fields!new_Maths.Value), 0, 1) + IIF(IsNothing(Fields!new_English.Value), 0, 1) + IIF(IsNothing(Fields!new_Computer.Value), 0, 1) + IIF(IsNothing(Fields!new_Science.Value), 0, 1)

- Following this in the
**Second Expression***(i.e. Aggregate 2)*, I just called the Total from my custom code.

=Code.Total

**Finally I got the correct result:-**

Hope this will be helpful.

**Thanks !!!**

😀

Mohsin Ali

said:Such kind of requirement is not required in past, to me. But I hope this hope will work for me in the future.

Thank You very much and keep it up.

Ashwani~Ashwin

said:Thanks for the Appreciation Mohsin !!!

Yavuz Filizlibay

said:very thanks, it works!

John

said:I don’t usually comment but I gotta admit thanks for the post on this great one

Smith

said:appreciate it for the post.

Jamie

said:Great post. How do you reset it though? My grouping is by column therefore need to clear it when it goes onto the next group. Thanks in advance

Erika

said:Thank you very much for this!

Gaga

said:hi.. your code is working super fine for one cell like you showed in the picture..how do i make it applicable for the full report.. Please advise

David

said:It works great, but as Jamie said, how do you reset it?

Flora

said:Helped me a bunch!! Thank you!!

Apu Nahasapeemapetalan

said:This post is be making me into having happy times. Happy times to readers of blog here. Gratitude for showing ways of doing.

Nabeel Haris

said:I am working on ERP and trying to make Ledger Report where I have to fix the OpeningBalance inText box and then use the value of that fixed opening Balance in Table to find the Total Balance But I m facing the same problem that aggregate Functions are only used in header and footer plz help me out

=RunningValue((ReportItems!Textbox57.Value+Fields!AMNTD.Value)-Fields!AMNTC.Value,Sum,”FKMAST”)