Crystal Reprots Formula help

Gerhardpet
Gerhardpet used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Richard QuadlingSenior Software Developer

Commented:
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.

Author

Commented:
I can't find anything. I'm on version 10...perhaps you are talking about a newer version?
Richard QuadlingSenior Software Developer

Commented:
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.

Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Author

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
Senior Software Developer
Commented:
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

Commented:
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
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
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

Author

Commented:
Thank you RQuadling. Your formula works great!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial