Sum iif in Report Builder 2.0

Question

Sum iif in Report Builder 2.0

clarmatt73

SSCrazy

Points: 2297
More actions
May 17, 2011 at 9:20 am

#238378

Hi All,
I am trying to get a sum iif to work in Report Builder 2.0, please see expression below:
=Sum(IIF(Fields!PaidDirecttoThree.Value = "Y", Fields!Amountcollected.Value, 0))
Everytime i run the report however i get an error. The PaidDirecttoThree column in the report is either Y or it is null.
Any ideas?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Charles Hearn SSCrazy Points: 2680 More actions · Answer 1

Without an error message it's hard to say but a quick guess is that you are trying to sum different data types. Since amount is probably a decimal type, try changing your expression to:

=Sum(IIF(Fields!PaidDirecttoThree.Value = "Y", Fields!Amountcollected.Value, 0.0))

clarmatt73 SSCrazy Points: 2297 More actions · Answer 2

Hi Charles,

Thanks for the response, unfortunately i've tried that and still got the same error, all i'm getting is #error where the figure should be in the report.

Charles Hearn SSCrazy Points: 2680 More actions · Answer 3

Any chance you could open the .rdl file in BIDS? You should get something more descriptive than #Error in the BIDS error list.

Gift Peddie SSC Guru Points: 73570 More actions · Answer 4

clarmatt73 (5/17/2011)
Hi Charles,
Thanks for the response, unfortunately i've tried that and still got the same error, all i'm getting is #error where the figure should be in the report.

The actual question is why is this code run in Report Builder to improve the results, when you can address most expected results related issues in code which is used to creat the reports.

Kind regards,
Gift Peddie

josephcanwork SSC Enthusiast Points: 187 More actions · Answer 5

=Sum(IIF(Fields!PaidDirecttoThree.Value = "Y", Fields!Amountcollected.Value, 0))
Everytime i run the report however i get an error. The PaidDirecttoThree column in the report is either Y or it is null.
Any ideas?

Instead of checking for "Y", shouldn't you check for nothing? I've run into problems before with fields that are nullable. A nullable field should be checked for null prior to trying a comparison. In this case, it's either null or Y, so you can just check for null and be done with it. 🙂

=SUM(IIF(ISNOTHING(Fields!PaidDirecttoThree.Value), 0, Fields!Amountcollected.Value))

clarmatt73 SSCrazy Points: 2297 More actions · Answer 6

Hi Joeseph,

I have tried the statement with ISNOTHING and it is still pulling up the same error, might have to give this up as a bad job and re-visit the code to see if i can make things easier.

Thanks to everyone for thier advise 🙂