Skip to Content
0

Crystal Reports - Filter a group before counting group

May 05, 2017 at 04:23 PM

53

avatar image

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!

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Abhilash Kumar
May 05, 2017 at 04:43 PM
0

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

Show 1 Share
10 |10000 characters needed characters left characters exceeded

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.

0
Cheryl Warner May 05, 2017 at 07:21 PM
0

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.

Share
10 |10000 characters needed characters left characters exceeded
Brian Dong May 05, 2017 at 09:02 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded