Crystal Reports null values to zero in formula

Technical Questions

Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions

Topic: null values to zero in formula

Author	Message
Megan10e Newbie Joined: 19 Aug 2010 Online Status: Offline Posts: 24	Topic: null values to zero in formula Posted: 09 Jul 2012 at 7:11am
	I need to use a formula in my report (order subtotal * order discount percentage), but when "order discount percentage" is null, those records disappear from my report. How can I write the formula so that the null values are regarded as zeros? Thank you for your help!
	IP Logged


kevlray Admin Group Joined: 29 Oct 2009 Online Status: Offline Posts: 1587	Posted: 09 Jul 2012 at 11:01am
	if isnull({order subtotal}) or isnull({order discount percentage}) then 0 else order subtotal * order discount percentage. Of course you would put in the actual fields names being used into the formula.
	IP Logged

Megan10e Newbie Joined: 19 Aug 2010 Online Status: Offline Posts: 24	Posted: 09 Jul 2012 at 11:27am
	Thanks, but it's still hiding the records that have a null value for the order discount percentage. If I run the report without the formula, the records all show up, but once I add it to the report, those records disappear. Otherwise the totals are showing up correctly.
	IP Logged

kevlray Admin Group Joined: 29 Oct 2009 Online Status: Offline Posts: 1587	Posted: 09 Jul 2012 at 12:27pm
	It should show a zero (unless you that suppressed for zero values).
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 10 Jul 2012 at 3:58am
	this is likely a join issue ignore your formula for a minute and just drag and drop the 'order discount percentage' field onto your report. Do your records still disappear? if so you have an issue with your joins which cannot be resolved in the formula Edited by DBlank - 10 Jul 2012 at 3:59am
	IP Logged

Megan10e Newbie Joined: 19 Aug 2010 Online Status: Offline Posts: 24	Posted: 10 Jul 2012 at 8:39am
	Yes, they still disappear. How would I resolve a join issue?
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 10 Jul 2012 at 8:58am
	depends... if it is a simple thing then you can go into the database expert and change your join from inner to an outer (left or right depends on which way your tables are joined). That being said, an outer join can be flipped back to an inner join if the select expert does not account for it correctly (and soemtimes cannot).
	IP Logged

Printable version

Forum Jump

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum