Complete Group suppression when data within group matches

[alexfromuno]

I'm running Crystal 8.5, Oracle 8.1.7 and can't seem to get the Selection Formula for a Group working correctly.

Here are the details:
Working at a hospital, trying to produce a list of all patients in a programs (Kidney, liver, heart, etc), that have no Primary Care Physician attached to that patient. There is more than 1 table involved in the query to get the program name (like Kidney, Liver, Heart, etc.), patient name and other items of interest, but basically, what I'm attempting to do is that, for a given patient and for all referring physicians for that patient, if there is 1 Primary care physician in that patients group, than do not print it.

Here's an example of SQL code:

SELECT prc.REFERRING_TYPE, pp.PROGRAM_ID, pp.PAT_ID,
rt.DESCRIPTION, pd.FIRST_NAME, pd.LAST_NAME, p.PROGRAM_NAME
FROM PAT_PROGRAM pp,
PATIENT_DEMO pd,
PROGRAMS p,
PAT_REF_CARE prc,
REFERRING_TYPES rt
WHERE (pp.PAT_ID = pd.PAT_ID)
AND (pp.PROGRAM_ID = p.PROGRAM_ID)
AND (pd.PAT_ID = prc.PAT_ID)
AND (prc.REFERRING_TYPE = rt.REFERRING_TYPE)
AND pp.PROGRAM_ID = 4
ORDER BY p.PROGRAM_NAME, pp.PAT_ID, pp.PROGRAM_ID

where program_id refers to the program (Liver, kidney, Heart) and the referring_type is the type of doctor
(1 = Primary, 2=referring, 3= attending, etc)

to repeat, what I'd like to do is be able to say that, when a patient has a primary care, then do not print that group (patient ID, anme and all reffering doctors associated with that patient) but only print groups with NO primary care doctor. Sorry if I'm a little long winded here, I've been fighting with this for days.

Thanks in advance.
alex

 

[jdemmi]

I am assuming that if the patient has no primary care doctor then the "referring_type" is NULL?

Well at least it would not be 1, 2, or 3, correct?

If that IS the case then go to the section expert.

From here select the SUPRESS check box and then click on the FORMULA button (x-2 with a pencil) to the right of the check box.

Here is where you will place the condition on which to supress.

In your case your formula could be:

not isnull({referring_type})

(this will supress the group when the referring type IS NOT NULL.

Or your formula could be:
{referring_type} = 1 OR {referring_type} = 2 OR {referring_type} = 3

I think this should work for you. Let me know


-- Jason
"It's Just Ones and Zeros

 

[alexfromuno]

Jason,

Thansk for the info. But, the primary care doctor IS a 1, attending = 2, admitting = 3, etc. But, I kind of figured it out myself by the following route.

In the section expert I created a formula called
@test-refering-type, withthe following code:

if {PAT_REF_CARE.REFERRING_TYPE} = 1 then 1 else 0

Then in both detail section and group section, under surpress, section expert, I placed the following code:

sum({@test-referring-type},{PAT_PROGRAM.PAT_ID})> 0

This works like a charm.

But, thanks anyway for the quick response. This site ROCKS!! (and so do you for helping!!)

 

[kskid]

If you want to prevent the patient from being selected altogether, then add this to Database->Show SQL Query.

 WHERE  (pp.PAT_ID = pd.PAT_ID) 
 AND     (pp.PROGRAM_ID = p.PROGRAM_ID) 
 AND     (pd.PAT_ID = prc.PAT_ID) 
 AND     (prc.REFERRING_TYPE = rt.REFERRING_TYPE) 
 AND     pp.PROGRAM_ID = 4
[b][blue] AND NOT EXISTS
(SELECT * 
 FROM   PAT_REF_CARE prc1
 WHERE  prc1.REFERRING_TYPE = '1'
 AND    prc1.PAT_ID = prc.PAT_ID)[/blue][/b]

Cheers,

-LW

 

[ssingh20]

Hi,

I think this is pretty old issue but I was trying to find a solution to my problem and I noticed the last post by LW. I used CR 10 and I have never been able to change the SQL Query using the way as explained above by LW. Do let me know if there is any other way to modify it.

Thanks,
ssingh20

 

[lbass]

In CR 10, depending upon your database, you could use "Add Command" as your datasource. This gives you the opportunity to write your SQL query from scratch.

-LB