Sum non-Blank fields in Report Builder not working

Question

I'm trying to use IIF to sum non-blank fields in a column in Report Builder, but it does no seem to be working.

This is the syntax I'm using and was wondering if it is correct:

=SUM(IIF(Fields!Child_Order_Reserve_Number.Value="",0,1))

There are two tables, one containing the view of all the data, and the other containing the above expression.  Both are connected to the same dataset.

The sum does not always match what is seen in the report.  For instance when I see 6 non-blank fields, it might say 2.

Answer 1

Hi,

Can you try?  =SUM(IIF(IsNothing(Fields!Child_Order_Reserve_Number.Value),0,1))

---

Please mark as answered or vote helpful if this post help resolved your issue. Thanks!

k r o o t z

Answer 2

I made that change and there was no change in the result.  So I take it that the above syntax is correct.  In that case, it must be something with the way I structured things?

Answer 3

Yes probably it's more on the structure of your report. Where exactly are you calling this expression? On a table, matrix, on a group header, etc. SHowing your report layout will help.

---

Please mark as answered or vote helpful if this post help resolved your issue. Thanks!

k r o o t z

Answer 4

krootz, it seemed to be stupid problem on my end.  I was looking at the wrong field when doing the sum command.  Incidentally, comparing it to "" rather than using IsNothing worked for me.  It's tough when doing things without live help or training.  At any rate I appreciate your help.