Sum of a derived field which is a sum itself
Hi Gurus,
I am trying to do:
Sum (IIf(Fields!Status.Value = "Completed", CountDistinct(Fields!Name.Value), 0),"DataSet1")
But I am getting "Couldn't use and aggregate funtion on an another aggregate function" etcc... error.
I am using BIDS SSRS 2008 . I dont know much of coding in .NET. Is there anything i can do in SSRS alone?
Please help me with this.
Thanks
June 22nd, 2012 3:02pm
So, for eg. say, you have 4 fields, f1, f2, f3, f4
You can right click on these individual fields and click on "Add Total" (Derived field in your case).So, in the next row, you will have values like:
f1 f2 f3 f4
10 20 30 40(--Result of doing "Add total", which is a sum of the values in f1, f2, f3, f4)
Now, what you want to do is sum of those totals (10+20+30+40). So, what you can do is an expression:
=Sum(Fields!f1.Value) + Sum(Fields!f2.Value) +Sum(Fields!f3.Value) + Sum(Fields!f4.Value)
Free Windows Admin Tool Kit Click here and download it now
June 22nd, 2012 3:35pm
So, for eg. say, you have 4 fields, f1, f2, f3, f4
You can right click on these individual fields and click on "Add Total" (Derived field in your case).So, in the next row, you will have values like:
f1 f2 f3 f4
10 20 30 40(--Result of doing "Add total", which is a sum of the values in f1, f2, f3, f4)
Now, what you want to do is sum of those totals (10+20+30+40). So, what you can do is an expression:
=Sum(Fields!f1.Value) + Sum(Fields!f2.Value) +Sum(Fields!f3.Value) + Sum(Fields!f4.Value)
Hi SqlCraze,
Lets say I have
f1 f2 f3 f4
10 20 30 40 - this above exp: Sum (IIf(Fields!Status.Value = "Completed", CountDistinct(Fields!Name.Value), 0),"DataSet1")
20 30 40 50 -Same
ttl: Sum(F4) I want like this. Let me know if this is not clear.
June 22nd, 2012 3:43pm
Forget about Sum (IIf(Fields!Status.Value = "Completed", CountDistinct(Fields!Name.Value), 0),"DataSet1").
Do you want the result of (40 + 50). The total of values in field f4?
Free Windows Admin Tool Kit Click here and download it now
June 22nd, 2012 3:45pm
Forget about Sum (IIf(Fields!Status.Value = "Completed", CountDistinct(Fields!Name.Value), 0),"DataSet1").
Do you want the result of (40 + 50). The total of values in field f4?
I want (40+50)=90 in last row which is total.text box.
June 22nd, 2012 3:55pm
Just, right click on Fieldf4. There will be an option of "Add Total". Click on that, and a new row will be added to your record set.
Also, you can do, something like this
=Sum(Fields!Field4.Value)
Hope that helps :)
Free Windows Admin Tool Kit Click here and download it now
June 22nd, 2012 5:46pm
Hi,
Just insert a row outside group below and place this expression
=Sum(Fields!Field4.Value)
Hope this will help you !!!
Sanjeewan
June 22nd, 2012 11:00pm
Hi There
Thanks for your posting. as Sanjeen said Just insert a row outside group below and place this expression like this, however if your Field4 is not a dataset field but a calculated field then you can use report item collection and you expression
migfht be this
=Sum(reportitems!Field4.Value)
--------------------------------------------------------------------------------
I hope this will help
Many thanks
Syed Qazafi Anjum
Free Windows Admin Tool Kit Click here and download it now
June 23rd, 2012 12:40am
Hi Guys,
I know how to do this for normal fields. But the exp I provided here must be evaluated. If you understand the expression you will know my problem.
this expression works fine for all the rows. :
=IIf(Fields!Status.Value ="Completed", CountDistinct(Fields!Name.Value), 0)
But for the total row. L I need to sum the above exp:
June 25th, 2012 1:10pm