Crystal reports - unable to create report to find empty fields

Posted on 2009-04-15
Last Modified: 2012-05-06
I am fairly new to Crystal reports, so unfamiliar with SQL and limited in Crystal formulas.  Keep running into the same problem.
I work in a health office, and need to find clients who are not compliant with vaccine requirements.  Initially, I pull all clients.  I then need to find persons who have not had a lab result that is immune, or have not had enough doses of vaccine, or have not had any vaccine or lab.  The labs and the vaccines are in two different tables and will only show in reports if the lab/vaccine has either been ordered or given.  If not ordered, then the field is blank.
If I use Select Expert to filter for only the names of the vaccines and/or labs, I lose all clients who have not had one or the other or both of them.  
If instead I try to use "if then" statements, the report is literally thousands of pages long, listing the same data over and over for each lab or vaccine the person has ever had. I then tried to suppress the data I didn't want, but still losing those who have never had lab and/or vaccine.
I've also tried grouping and dragging the result into the group footer, and using Detail A & B sections to separate labs and vaccines, but none of it works.  I should show 6146 clients, but only see 6071, and it's the missing 75 that I need to address!  Help!
Here is a sample of one of the many formulas I've tried:


numbervar nVaricella1;

numbervar nVaricella2;

numbervar nVaricella3;

numbervar nVaricella4;

numbervar nVaricella5;

numbervar nVaricella6;

if {Shots.ShotID} = 'Varivax' then  nVaricella1 := 1;

if {Shots.ShotID} = "Varivax1" then  nVaricella2 := 1;

if {Shots.ShotID} = "Varivax2" then  nVaricella3 := 1;

if {Shots.ShotID} = "Varxdecl" then  nVaricella4 := 1;

if {Shots.ShotID} = "varicell" then  nVaricella5 := 1;

if ({Lab.TestID} = "variclti" and {Lab.Labresults} = "immune") then nVaricella6 := 1;

if ((nVaricella1 + nVaricella2 + nVaricella3 >=2) or  nVaricella4 = 1 or nVaricella5 = 1 or nVaricella6 = 1)  then "Immune" else "Susceptible"

Open in new window

Question by:ehathaw1
    1 Comment
    LVL 100

    Accepted Solution

    You need to use a left outer join from the client table to the other tables.
    You cant filter on the other tales since Crystal turns the outer join into an inner join since NULL cannot equal your value.

    What do you get if you don't filter the report?


    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    Suggested Solutions

    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…
    There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    This video discusses moving either the default database or any database to a new volume.

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now