Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Addition with NULL value !!! Post Reply Post New Topic
Page  of 2 Next >>
Author Message
karthizen
Newbie
Newbie
Avatar

Joined: 10 Dec 2008
Location: India
Online Status: Offline
Posts: 17
Quote karthizen Replybullet Topic: Addition with NULL value !!!
    Posted: 10 Dec 2008 at 1:46am

I am performing simple addition operation, adding values from 6 columns. The value of the 6th column is null.

Formula:

({TAX_CALC.TAX_AMOUNT1}+{TAX_CALC.TAX_AMOUNT2}+{TAX_CALC.TAX_AMOUNT3}+{TAX_CALC.TAX_AMOUNT4}+{TAX_CALC.TAX_AMOUNT5}+{TAX_CALC.TAX_AMOUNT6})

As CR encountered a null valued field in the formula it stopped evaluating the formula and produces no value..

How to solve this?

From MSDN I found that I need to ISIfnull before the formula...
 
To be honest, I am very new to this & not aware of how to solve ....
 
It would be great if someone helps me out !
 
Regards,
Karthik...
IP IP Logged
rahulwalawalkar
Senior Member
Senior Member
Avatar

Joined: 08 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 731
Quote rahulwalawalkar Replybullet Posted: 10 Dec 2008 at 2:39am
Hi
 
Check using the code below and put a if else condition
 
If isnull(({TAX_CALC.TAX_AMOUNT1}).... and so on then
0
else
{TAX_CALC.TAX_AMOUNT1}+{TAX_CALC.TAX_AMOUNT2}+{TAX_CALC.TAX_AMOUNT3}+{TAX_CALC.TAX_AMOUNT4}+{TAX_CALC.TAX_AMOUNT5}+{TAX_CALC.TAX_AMOUNT6})
 
 


Edited by rahulwalawalkar - 10 Dec 2008 at 2:39am
IP IP Logged
karthizen
Newbie
Newbie
Avatar

Joined: 10 Dec 2008
Location: India
Online Status: Offline
Posts: 17
Quote karthizen Replybullet Posted: 10 Dec 2008 at 3:01am

Thanks Rahul,

I used the formula in the follwing way, however it doesnot works...Please correct If I had done any mistakes...

if isnull ({TAX_CALC.TAX_AMOUNT1}) then 0
else if isnull ({TAX_CALC.TAX_AMOUNT2}) then 0
else if isnull ({TAX_CALC.TAX_AMOUNT3}) then 0
else if isnull ({TAX_CALC.TAX_AMOUNT4}) then 0
else if isnull ({TAX_CALC.TAX_AMOUNT5}) then 0
else if isnull ({TAX_CALC.TAX_AMOUNT6}) then 0
else
{TAX_CALC.TAX_AMOUNT1}+{TAX_CALC.TAX_AMOUNT2}+{TAX_CALC.TAX_AMOUNT3}+{TAX_CALC.TAX_AMOUNT4}+{TAX_CALC.TAX_AMOUNT5}+{TAX_CALC.TAX_AMOUNT6}

 

 

 
Regards,
Karthik...
IP IP Logged
rahulwalawalkar
Senior Member
Senior Member
Avatar

Joined: 08 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 731
Quote rahulwalawalkar Replybullet Posted: 10 Dec 2008 at 3:07am
Hi
Can you let me know what doesnot work.....Also if you could post the results and expected results will help
 
Cheers
Rahul
IP IP Logged
rahulwalawalkar
Senior Member
Senior Member
Avatar

Joined: 08 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 731
Quote rahulwalawalkar Replybullet Posted: 10 Dec 2008 at 3:16am
Hi
 
Try this open the report,the go to File Menu Report options in that
Check Convert Database Null Values to Default
 
Cheers
Rahul
IP IP Logged
karthizen
Newbie
Newbie
Avatar

Joined: 10 Dec 2008
Location: India
Online Status: Offline
Posts: 17
Quote karthizen Replybullet Posted: 10 Dec 2008 at 3:20am

If any one of the Tax amount is NULL then the entire output of the formula sets to ZERO.

I have found an alternate by having six formulae i.e for each tax like,

Tax1:
 
if isnull ({TAX_CALC.TAX_AMOUNT1}) then 0
else
{TAX_CALC.TAX_AMOUNT1}
.
.
.
Tax6:
if isnull ({TAX_CALC.TAX_AMOUNT6}) then 0
else
{TAX_CALC.TAX_AMOUNT6}
 
and then finally have a formula to add all these formulae
 
like Total tax = Tax1+Tax2+Tax3+Tax4+Tax5+Tax6....
 
 
but I need a single formula to solve this...

 

 
Regards,
Karthik...
IP IP Logged
rahulwalawalkar
Senior Member
Senior Member
Avatar

Joined: 08 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 731
Quote rahulwalawalkar Replybullet Posted: 10 Dec 2008 at 3:28am
Hi
 
Try this open the report,the go to File Menu Report options in that
Check Convert Database Null Values to Default
 
Cheers
Rahul
IP IP Logged
karthizen
Newbie
Newbie
Avatar

Joined: 10 Dec 2008
Location: India
Online Status: Offline
Posts: 17
Quote karthizen Replybullet Posted: 10 Dec 2008 at 3:29am
Convert Database Null Values to Default
 
 
This method works fine Rahul.... But I need a formula .....Cry
Regards,
Karthik...
IP IP Logged
rahulwalawalkar
Senior Member
Senior Member
Avatar

Joined: 08 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 731
Quote rahulwalawalkar Replybullet Posted: 10 Dec 2008 at 3:31am
Hi
 
if isnull ({TAX_CALC.TAX_AMOUNT1}) then 0
else if isnull ({TAX_CALC.TAX_AMOUNT2}) then 0
else if isnull ({TAX_CALC.TAX_AMOUNT3}) then 0
else if isnull ({TAX_CALC.TAX_AMOUNT4}) then 0
else if isnull ({TAX_CALC.TAX_AMOUNT5}) then 0
else if isnull ({TAX_CALC.TAX_AMOUNT6}) then 0
else
{TAX_CALC.TAX_AMOUNT1}+{TAX_CALC.TAX_AMOUNT2}+{TAX_CALC.TAX_AMOUNT3}+{TAX_CALC.TAX_AMOUNT4}+{TAX_CALC.TAX_AMOUNT5}+{TAX_CALC.TAX_AMOUNT6
 
The formula is same as previous
 
Cheers
Rahul
IP IP Logged
karthizen
Newbie
Newbie
Avatar

Joined: 10 Dec 2008
Location: India
Online Status: Offline
Posts: 17
Quote karthizen Replybullet Posted: 10 Dec 2008 at 3:52am
Even I thought the two would be same.... But if I use the above one I am not getting the desired result....
 
It gives just zero.....
 
donno why.....
Regards,
Karthik...
IP IP Logged
Page  of 2 Next >>
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.031 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.