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

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics