Ask Your Question

How do I sum fields in a report and ignore blanks [closed]

asked 2013-07-18 18:37:09 +0100

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

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?


edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2015-11-08 08:51:00.354216

2 Answers

Sort by » oldest newest most voted

answered 2013-07-19 16:20:21 +0100

w_whalley gravatar image

updated 2013-07-19 17:45:12 +0100

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)

edit flag offensive delete link more


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.

Taft gravatar imageTaft ( 2013-07-22 15:17:20 +0100 )edit

answered 2013-09-13 17:16:33 +0100

Libre Lyrae gravatar image

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])


edit flag offensive delete link more

Question Tools

1 follower


Asked: 2013-07-18 18:37:09 +0100

Seen: 1,672 times

Last updated: Sep 13 '13