Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 246
  • Last Modified:

Crystal Reprots Formula help

I have 5 tables and in each table a field for the Year-To-Date Sales in numbers

I created a formula that will add up all the Year-To-Date Sales from all 5 tables.

The formula works if every table has the same record but if one table does not have one record then I get nothing back.

Here is the formula. Can someone help me to make this work?
 
{NDC_WHSE.YTD_SALES}+{609_WHSE.YTD_SALES}+{BIR_WHSE.YTD_SALES}+{DUF_WHSE.YTD_SALES}+{WPG_WHSE.YTD_SALES}

Open in new window

0
Gerhardpet
Asked:
Gerhardpet
1 Solution
 
Richard QuadlingSenior Software DeverloperCommented:
In the formula editor, you should see a little box regarding how to handle nulls.

It is probably set to "Exceptions for Nulls".

Change it to "Default Values for Nulls" and your formula should work again.

See image below.

Crystal Reports Formula Editing Null Handling.
0
 
GerhardpetAuthor Commented:
I can't find anything. I'm on version 10...perhaps you are talking about a newer version?
0
 
Richard QuadlingSenior Software DeverloperCommented:
In which case, you need to use the isNull(fld) function to determine if the fld is null first.

But I'm really sure that this option is there in older versions of Crystal.

0
[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

 
GerhardpetAuthor Commented:
I'm not an expert in Crystal but this is what I when I try to edit a formula. Here is a sreenshoot
www.iversa.ca\files\Crystal.jpg
0
 
Richard QuadlingSenior Software DeverloperCommented:
Can you try
if isnull({NDC_WHSE.YTD_SALES}) then 0 else {NDC_WHSE.YTD_SALES}
+
if isnull({609_WHSE.YTD_SALES}) then 0 else {609_WHSE.YTD_SALES}
+
if isnull({BIR_WHSE.YTD_SALES}) then 0 else {BIR_WHSE.YTD_SALES}
+
if isnull({DUF_WHSE.YTD_SALES}) then 0 else {DUF_WHSE.YTD_SALES}
+
if isnull({WPG_WHSE.YTD_SALES}) then 0 else {WPG_WHSE.YTD_SALES}

Open in new window

0
 
GJParkerCommented:
How are the 5 tables joined together ? Can the year-to-date field be missing from any of the 5 tables or just one.

If it can be missing from any of the 5 tables the you'll need to use FULL OUTER JOINS to join the tables together but this may not work if you have constarints in the record selection formula on any of the tables.

You'll probably have more luck if you create a command as your datasource and UNION the 5 tables together, group on the ID field in the report and you can then sum the YTD_SALES field

HTH
0
 
mlmccCommented:
CR XI (v11) added the Drodown for the NULL issue.

Do the tables have the same structure?
If so then the union idea has some merit.

mlmcc
0
 
GerhardpetAuthor Commented:
Thank you RQuadling. Your formula works great!!
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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