Addition with NULL value !!!
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=4969
Printed Date: 28 Apr 2024 at 8:38pm
Topic: Addition with NULL value !!!
Posted By: karthizen
Subject: Addition with NULL value !!!
Date 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... |
Replies:
Posted By: rahulwalawalkar
Date 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})
|
Posted By: karthizen
Date 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
------------- Regards, Karthik... |
Posted By: rahulwalawalkar
Date 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
|
Posted By: rahulwalawalkar
Date 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
|
Posted By: karthizen
Date 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... |
Posted By: rahulwalawalkar
Date 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
|
Posted By: karthizen
Date Posted: 10 Dec 2008 at 3:29am
|
Convert Database Null Values to Default
This method works fine Rahul.... But I need a formula .....  ------------- Regards, Karthik... |
Posted By: rahulwalawalkar
Date 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
|
Posted By: karthizen
Date 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... |
Posted By: rahulwalawalkar
Date Posted: 10 Dec 2008 at 3:57am
|
Hi,
i.e. Strange,I have tested the report created table in sql server 2005 with 6 numeric values one value is null used crystal XI to create the report used OLEDB.
and it doesn't give me zero it gives me the correct value
Cheers
Rahul
|
Posted By: rahulwalawalkar
Date Posted: 10 Dec 2008 at 4:15am
|
Hi
Here is another method use SQL
select ISNULL(t1, 0) +ISNULL(t2, 0)+ ISNULL(t3, 0) + ISNULL(t4, 0) + ISNULL(t5, 0) + isnull(t6,0)from Tax_t
Cheers
Rahul |
Posted By: karthizen
Date Posted: 10 Dec 2008 at 6:13am
|
Thank you rahul..... I will check out again and let you know.
------------- Regards, Karthik... |
Posted By: RitaInHood
Date Posted: 10 Dec 2008 at 1:15pm
|
How about if isnull ({TAX_CALC.TAX_AMOUNT1}) then a:=0, else a:={TAX_CALC.TAX_AMOUNT1}; if isnull ({TAX_CALC.TAX_AMOUNT2}) then b:=0, else b:={TAX_CALC.TAX_AMOUNT2}; . . etc . .. result:=a+b+c+d+e+f; |
Posted By: karthizen
Date Posted: 10 Dec 2008 at 11:24pm
|
Works good yaar !
Thanks ! ------------- Regards, Karthik... |
Posted By: karthizen
Date Posted: 11 Dec 2008 at 3:27am
|
How about this?
Formula:
(if isnull ({TAX_INFO.TAX_AMOUNT_1}) then 0 else ({TAX_INFO.TAX_AMOUNT_1})) + (if isnull ({TAX_INFO.TAX_AMOUNT_2}) then 0 else ({TAX_INFO.TAX_AMOUNT_2})) + (if isnull ({TAX_INFO.TAX_AMOUNT_3}) then 0 else ({TAX_INFO.TAX_AMOUNT_3})) + (if isnull ({TAX_INFO.TAX_AMOUNT_4}) then 0 else ({TAX_INFO.TAX_AMOUNT_4})) + (if isnull ({TAX_INFO.TAX_AMOUNT_5}) then 0 else ({TAX_INFO.TAX_AMOUNT_5})) + (if isnull ({TAX_INFO.TAX_AMOUNT_6}) then 0 else ({TAX_INFO.TAX_AMOUNT_6})) ------------- Regards, Karthik... |