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

Crystal reports - unable to create report to find empty fields

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

1 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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