We help IT Professionals succeed at work.

Crystal Report details doubling

I am attaching all the info for this question.  Why, when I add a particular formula to the detail section, do I get doubles of everything that was already there?  I looked at my links (also in attached) and don't see anything that should make this happen.  I need this information to show for each patient, so somehow I have to fix this.  

The formula causing the issue is:
"If {IMMUNE.IMMUNZATN_ID} in [21,23,2102] then "Flu on"+totext({@ImmuneDAte})
   else if {IMMUNE.IMMUNZATN_ID} in [53,54] then "Pneum on"+totext({@ImmuneDAte})"
and the Immune Date formula is:
"Date({IMMUNE.IMMUNE_DATE})"


Also, there are 3 grouping levels in this report.  Location, department, patient.  If this information would work in the patient grouping, then we could split the formula into 2 formulas, one for pneumonia and one for flu vaccinations.  That was the initial build, but as soon as the formulas were added to the section, the detail info doubled up, so I combined them into one formula thinking that might help.  It did not.
150885-example.pdf
Comment
Watch Question

Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Crystal doesn't include the data from a table until you use a field from it.  Apparently you have a 1 to many relationship between the Patient and Immune tables.

If you add a group on patient and put the common information in the patient header the only information for details will be the information on immunizations.

mlmcc
Becky EdwardsEpic Clarity Developer

Author

Commented:
All my links are inner joins.  I have a group on patient and the common information is in the group.  IE Admit Date.  But when I add immunization date to the group, everything in the details section doubles, I assume because the patient had two immunizations.  How do I stop this?
Also, this may make a difference, in the suppression area of the detail section is this formula:
not({IP_FLWSHT_MEAS.FLO_MEAS_ID} in makearray("1540100031","1540100032","1540100037","3040103317"))

It is there to make sure that everything else in the chart that happened to the patient does not display EXCEPT for these four things, which are all related to the immunizations.  Would this make a difference?  Would the Makearray wording cause this?
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Is MakeArray a Crystal function?

Can you upload the report?

mlmcc
 mlmcc,

 FWIW, I checked, and MakeArray is there.  It does just what you'd expect.  Same as putting the list of values in [].


 bjrhart,

 I agree that it sounds like you just have multiple IMMUNE records, so when you add any references to that table, you'll get one detail record for each of those records.  Presumably you want to see each immunization, so the report has to read those records.

 What is being "doubled", exactly?  Are you doing some totals of the patient information that are inflated, or just seeing the detail fields listed twice?  If the problem is just seeing the details listed more than once, you might be able to do something like put the fields that you don't want repeated in the patient group header (or possibly the header for some new group, if your "detail" level is something below that, like hospital visits), and leave the immunization fields in the detail section.  If the problem is inflated totals, you may have to use running totals (set to evaluate once per patient, or whatever your detail level is), or formulas and variables.

 As for your suppression formula:

 MakeArray itself shouldn't be causing any problems.

 When you say "make sure that everything else in the chart that happened to the patient does not display EXCEPT for these four things", do you want to exclude the other FLO_MEAS_ID values from the report entirely, or just not show them in that part of the report?  If you want to exclude them entirely, add that test, without the "not" to the record selection formula (in which case you wouldn't need the suppression formula anymore).

 That would make the report more efficient, but if you're still including the immunizations, and it's the immunizations that are causing the records to be "doubled", then putting that test in the record selection presumably won't have any effect on the "doubling" (unless there are some types of immunization that you are not including, and those are the ones with the multiple records).

 James
Becky EdwardsEpic Clarity Developer

Author

Commented:
Moving the filter regarding flo_meas_id to the record select.  That was a great idea, don't know why I didn't think of it.  

BUTTTTTTT, still can't get all these details to show up correctly.  I am able to only show them once, by creating formulas for each flo_meas_id and creating a group on each formula, then putting the display formula in each group.  But I still can't get both vaccination dates to show up, even after creating two formulas, one for each vaccination.
Copy-of-KHN-Flu-Pneumonia-valida.txt
Becky EdwardsEpic Clarity Developer

Author

Commented:
Now I am able to get both Flu and Pneum vaccines in there by changing the formula to immunization time.  Actually I don't know if that is what did it but they are both in there.  BUT now I am seeing multiple times, one for each flo meas.  See attached.  So close!!!!!!
Copy-of-Flu-Pneumonia-VIP.txt
KHN-Flu-Pneumonia-validation.pdf
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Try using the SELECT DISTINCT RECORDS under the DATABASE menu

Also you can upload RPT files.

Can you build a report using just the Patient table and the Immunization table?

mlmcc
Becky EdwardsEpic Clarity Developer

Author

Commented:
Mimcc:  are you suggesting I build a report with just the patient and immune tables?  I can do that.  I am getting the feeling I will need to put the rest of the stuff in a subreport or something, to keep it from doubling.
Senior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
I am suggesting the 2 tables simply to see if you are  getting duplicates there.  It is simply to try to find where the duplicates are coming from.

mlmcc
Becky EdwardsEpic Clarity Developer

Author

Commented:
I think I know where the dulicates are coming from.  If you look at the samples attached, I am getting a set of records for each immunization.  One for the flu shot and one for the pneumonia shot.
Assuming that I'm following what you've been saying, that would be normal.  Let's say that you had an invoice header table with one record for each invoice, and a detail table with one record for each item on each invoice.  When you joined those two tables in a report, you would get one record for each item on each invoice.  In your case, you apparently have a patient table and an IMMUNE table.  When you join those, the report will read one record for each IMMUNE record for each patient (if the record meets the conditions in your record selection formula).  They're not really "duplicates", because they're for different immunizations.

 Normally you would handle something like this by having a group on patient, putting the basic patient info (name, etc.) in the group header or footer, and putting the immunization info in the detail section.

 James
Becky EdwardsEpic Clarity Developer

Author

Commented:
I have done as mimcc suggested and created a report with just the patient and immune tables in it.  I get the proper listing of immunizations, without duplications.  
PatientImmunRpt.rpt
Becky EdwardsEpic Clarity Developer

Author

Commented:
Doing a combination of what you both suggested, trying different combinations of tables to find the duplicates, worked.  I found where the patient had two encounters, which caused everything to double.  Grouping on encounter date helps.  It doesn't remove the duplicates, but at least now they look like they are there for a reason.

THank you both for your assistance in this.
Glad you were able to work it out.

 James