How do I sum fields in a report and ignore blanks

[Taft]

I have been trying to write a report for a purchase order database which totals the purchase order value. I can get the function to work in part using: [function_name] + [field_name].

This works for most purchase orders, however some purchase orders have a null value against an item cost. This is because users have added an item record to expand on the service they require rather than keeping it all in one field.

To accommodate this I amended the function to: [function_name] + IF([field_name]=NULL;0;[field_name]), but this gives me a completely blank total field. where purchase orders contain null item values.

I have also tried IF(ISBLANK… and get exactly the same problem.

Can anyone help?

Thanks

[w_whalley]

I’m not sure I totally understand how you are using this, but try replacing your
IF([field_name]=NULL;0;[field_name])
with
(case when "field_name" is null then 0 else "field_name" end)

[Taft]

Thanks for the response. In the report builder I created a group by Purchase Order Numbers and then added a function under this group which links to a formatted field which is located in the group footer. I tried your suggestion, but got the same result as the function I used.

Thanks for your suggestion though.

[LibreLyrae]

This is what I used to get the formatted field to calculate properly on an invoice report I created where it needed to add the totals even if there were null values:
[AccumulationItem Extended PriceReport]+IF(ISBLANK([ShippingCost]);0;[ShippingCost])+IF(ISBLANK([Shop Supply Fee]);0;[Shop Supply Fee])+IF(ISBLANK([OrderAdjustments]);0;[OrderAdjustments]*[AccumulationItem Extended PriceReport])+[AccumulationItem PSTReport]+[AccumulationItem GSTReport]

My issue is that I cannot get the fields themselves to display as zero, I tried the CASE WHEN syntax suggested in the above answer in my queries without success, In reports I tried this as well without success:
IF(ISBLANK([OrderAdjustments]);0;[OrderAdjustments])

Anyone?