Crystal Reprots Formula help

Posted on 2011-09-07
Last Modified: 2012-05-12
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?

Open in new window

Question by:Gerhardpet
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 40

Expert Comment

by:Richard Quadling
ID: 36495587
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 Comment

ID: 36495621
I can't find anything. I'm on version 10...perhaps you are talking about a newer version?
LVL 40

Expert Comment

by:Richard Quadling
ID: 36495688
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.

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.


Author Comment

ID: 36495788
I'm not an expert in Crystal but this is what I when I try to edit a formula. Here is a sreenshoot\files\Crystal.jpg
LVL 40

Accepted Solution

Richard Quadling earned 500 total points
ID: 36496092
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

LVL 19

Expert Comment

ID: 36496646
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

LVL 100

Expert Comment

ID: 36496813
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.


Author Closing Comment

ID: 36497494
Thank you RQuadling. Your formula works great!!

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
Suggested Courses

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question