Crystal Reports - Filter a group before counting group

[cwarner]

I have a report that needs 2 criteria:

1. Patients who do not have a custom note in the database with subject of "Ladder".

2. Patients with 6 or less visits to our clinic in the past.

The problem is, a patient could have more than 6 "custom notes", so this creates duplicate entries - correctly, as it is a 1 to many relationship.

However, this creates "more than 6 records", so patients who should be included in the report are omitted because I have a group selection formula limiting the records to be "6 or less for each patient".

If I do only a "record" select to delete the notes with "Ladder" in the subject, it only deletes those records, but keeps other records for that patient, since there are "other" notes not being deleted. The "Ladder" criteria must be looked at as a group.

Here is my current group formula, which works correctly until I add a formula to filter the "Ladder" notes out:

(Count({vwGenSvcInfo.Primary_Diagnosis_Code},{Appointments.Appointment_DateTime}) <= 6

Any help is appreciated!

[former_member292966]

Hi Cheryl,

One of Crystal's faults is not being able to do multi process querying. For you what you want, I would have a query return those patients without a Ladder note. Then I would have it query again for those with 6 or less visits.

One way to do this in Crystal is possibly with a subreport. Have the main report return all the patients without a Ladder note then have the subreport show the patients with 6 visits or less.

I would actually do this in a stored procedure. It would keep the logic in one place and just use Crystal to format the records. I found the more complicated the report, the better it is to do the logic in the backend. At times, the processing is better this way because Crystal may not be able to generate the SQL properly and can be very inefficient.

Good luck,

Brian

[cwarner]

Thanks, but it is still creating the same problem. I am missing a bunch of patients because they have other notes with subjects other than "Ladder", and so this pushes the record "count" on those patients to over 6; thus, deleting them from the report.

[abhilash_kumar]

Hi Cheryl,

1. Create a formula (@subject) with this code:

if {Subject} = "Ladder" then 1

2. Modify the Group Selection formula to:

Count({vwGenSvcInfo.Primary_Diagnosis_Code},{Appointments.Appointment_DateTime}) <= 6 AND
Maximum({@Subject}, {Appointments.Appointment_DateTime}) = 0

-Abhilash

[cwarner]

Thanks, but it is still creating the same problem. I am missing a bunch of patients because they have other notes with subjects other than "Ladder", and so this pushes the record "count" on those patients to over 6; thus, deleting them from the report.