ehathaw1
asked on
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:
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:
whileprintingrecords;
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"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.