Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Multiple if isnull then Post Reply Post New Topic
Author Message
PaulT
Newbie
Newbie
Avatar

Joined: 18 Apr 2007
Location: United Kingdom
Online Status: Offline
Posts: 3
Quote PaulT Replybullet Topic: Multiple if isnull then
    Posted: 18 Apr 2007 at 7:42am

I'm new to crystal so hopefully my question will have a simple answer!

I have created a report which has three columns (Report Fields). Lets call them {A} {B} and {C}.
 
I need to add a fourth column which adds the three columns up. But it won't do it if any of the three columns is blank ie(nul). 
 
To get around the problem of column A being null I can use
 
If isnull({A}) then
{B}+{C} else
{A}+ {B} +{C}.........
 
but this doesn't help if columns B and C are Null. I tried to use a kind of multiple 'If is null ' but no luck.
 
Any help much appreciated.
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet Posted: 18 Apr 2007 at 7:49am
You can tell Crystal Reports to automatically convert NULL values to 0. That way you're formulas won't get screwed up. Look under File | Options and click the Reporting tab.
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 18 Apr 2007 at 9:18am

Althought Brian's suggestion is the "right" way to do this, you could also use a set of formulas:

if IsNull({A}) then 0 else {A}
 
if IsNull({B}) the 0 else {B}
 
if IsNull{C} then 0 else {C}
 
{@A} + {@B} + {@C}
 
-Dell
IP IP Logged
PaulT
Newbie
Newbie
Avatar

Joined: 18 Apr 2007
Location: United Kingdom
Online Status: Offline
Posts: 3
Quote PaulT Replybullet Posted: 18 Apr 2007 at 10:22am
that sounds like what I need.
But when I look under file / options I get three options
 
1 Convert database NULL values to default
2 Convert Other NULL values to default
3 Always sort locally
 
I've tried ticking various comibinations of these and it hasn't helped yet.
 
do i need to set my defualt to  0 - if so where can i do that?
 
Many thanks
IP IP Logged
PaulT
Newbie
Newbie
Avatar

Joined: 18 Apr 2007
Location: United Kingdom
Online Status: Offline
Posts: 3
Quote PaulT Replybullet Posted: 18 Apr 2007 at 11:16am
Hilfy
 
yes your answer was where my thinking was heading,  but I just didn't know how to do it! 
I've set up new formulas as you suggested and it has worked!
 
Although I think for future it will be simpler for me set the NULL default setting to 0. So i still want to try and find out how to do this as currently i've not managed that.
 
Many thanks indeed.Big%20smile
 
 
IP IP Logged
Post Reply Post New Topic
Printable version 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



This page was generated in 0.016 seconds.