• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 318
  • Last Modified:

value in formula


{Trans.Open_Bal} + {PrvAmt.Vasuli} - {PrvAmt.Cross} - {PrvAmt.Amt_Rcvd}
eg..
1000 + 500 - 300 - 400
Result is  800

but when value is zero than it should be display at-least any result
eg..
1000 + 0 - 0 - 0
Result shoud be 1000
but its not
formula result is nothing (empty)
0
crystal_Tech
Asked:
crystal_Tech
1 Solution
 
teedo757Commented:
Try adding quotes to 0 and see if it helps

'0'
or "0"
0
 
TommySzalapskiCommented:
My guess is that you don't have 0 in those, you have NULLs. NULLs mess things up. Try it like this and see if it works:
(If isnull({Trans.Open_Bal}) then 0 else {Trans.Open_Bal}) + (If isnull({PrvAmt.Vasuli}) then 0 else {PrvAmt.Vasuli})  - (If isnull({PrvAmt.Cross}) then 0 else {PrvAmt.Cross}) - (If isnull({PrvAmt.Amt_Rcvd}) then 0 else {PrvAmt.Amt_Rcvd})
0
 
TommySzalapskiCommented:
A better way would be to do SELECT isnull(Trans.Open_Bal, 0) AS Open_Bal in the SQL query because the SQL isnull actually just replaces the null value with the second argument (0 here) so it saves a lot of typing.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
mlmccCommented:
Another way is in the formula choose the option
 DEFAULT VALUE FOR NULL rather than EXCEPTION FOR NULL

mlmcc
0
 
mlmccCommented:
Is this using CR 7?

If so then the NULL test is  the only way to go unless you can build a query in the database.

There is a report level option to use a default value for NULL.  That will work but it also substitutes default value for the NULL fields which can cause other issues.

mlmcc
0
 
TommySzalapskiCommented:
I assumed it was CR 7 since that's what was tagged that's why I suggested the NULL test and the query change.
0
 
crystal_TechAuthor Commented:
Thanks TommySzalapski:
your suggested NULL test is working

(If isnull({Trans.Open_Bal}) then 0 else {Trans.Open_Bal}) + (If isnull({PrvAmt.Vasuli}) then 0 else {PrvAmt.Vasuli})  - (If isnull({PrvAmt.Cross}) then 0 else {PrvAmt.Cross}) - (If isnull({PrvAmt.Amt_Rcvd}) then 0 else {PrvAmt.Amt_Rcvd})

and  your suggested query is also working with one of my another problem.
SELECT isnull(Trans.Open_Bal, 0) AS Open_Ba

Thanks Again
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now