How to fix "#Error" on custom field for Totals...

Question

User1178782284 posted

We have a column in an RDLC Report that shows a Dollar Amount if the amount in amount22 from the SQL Query is NOT zero. The Amount contains a Zero or an actual amount. IF the amount is zero, then we don't want to show "0" on the report because that would be unneeded clutter on the report.

Column Detail for placing the value or nothing in the RDLC Report:
=iif(not((Fields!amount22.Value = 0 or Fields!amount22.Value = "")), FORMAT(Fields!amount22.Value/100, "C"), "")

*** This works properly. As in we ONLY want to see the value if it is not zero on the report.

Total for the same field at the bottom of the report uses:
=sum(iif(not((Fields!amount22.Value = 0 or Fields!amount22.Value = "" or Fields!amount22.Value Is Nothing)), FORMAT(Fields!amount22.Value/100, "C"), 0))

This gives "#Error" on the Total Box.


Any hints on how to fix this annoying error on the report?

Answer 1

User1644755831 posted

Hello BillNew1957,

Please see this: http://stackoverflow.com/questions/14276648/rdlc-report-doesnt-detect-null-values-correctly

May be you can just reverse the condition assuming they are all positive since money. if you want negative values also then just add OR Fields!Amount22.Value < 0  condition too.

=IIF(Fields!Amount22.Value > 0 ,FORMAT(Fields!amount22.Value/100, "C"),"")

Hope this helps.

With Regards,

Krunal Parekh

Answer 2

User1178782284 posted

This fixed the issue with the Number Format for that total being "C":

=sum(iif(not((Fields!amount22.Value = 0 or Fields!amount22.Value = "")), Fields!amount22.Value/100, cdbl(0)))