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
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
ASKER
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_ME
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?
Also, this may make a difference, in the suppression area of the detail section is this formula:
not({IP_FLWSHT_MEAS.FLO_ME
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?
Is MakeArray a Crystal function?
Can you upload the report?
mlmcc
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
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
ASKER
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
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
ASKER
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
Copy-of-Flu-Pneumonia-VIP.txt
KHN-Flu-Pneumonia-validation.pdf
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
Also you can upload RPT files.
Can you build a report using just the Patient table and the Immunization table?
mlmcc
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
PatientImmunRpt.rpt
ASKER
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.
THank you both for your assistance in this.
Glad you were able to work it out.
James
James
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