CODEWITHSUNDEEP
reporting-services
Leonardo Wildt
Leonardo Wildt

Reputation: 2599

Specify Dataset in SSRS aggregate function

I am using an aggregate function to get value of a column based on the value of another column. I ran into this error when including a second data set in my report. 

=Sum(IIF(FIRST(Fields!LOB.Value, "DataSet1")  = "23",  (CInt(Fields!Cash_Value.Value, "DataSet1")), 0))

What i am trying to say is that if the first field has a value of 23 then get the value of the other field where it says 23.

Im getting two errors one is aggregate in aggregate expression and the other is missing aggregate scope. How should i change my expression to get the value i need? Thanks

Upvotes: 0

Views: 693

Answers (1)

R. Richards
R. Richards

Reputation: 25161

Yeah, you're trying to do some things you can't in SSRS, like nesting aggregate functions.

I think the following should do the trick for you.

=IIf(First(Fields!LOB.Value, "DataSet1")  = "23",  Sum(CInt(Fields!Cash_Value.Value), "DataSet1"), 0)

All I did was move the Sum inside the IIf, and rearranged the parens to get this to work. I only tested this with a couple of records, so don't be surprised if you have to play around with it to get it working in your report.

Good luck!

Upvotes: 1

Related Questions