Syntax Needed 1

[JMB7]

Crystal 8.5

I need to write report that returns patients that have multiple qualifications of the same field. Example: Need patients who have had hosp_svc equal to 'SRC' and 'SDS' and 'GNC' over the past year.
I have tried the following-
hosp_svc in ('SRC','SDS','GNC')

hosp_svc in SRC and
hosp_svc in SDS and ....

but it is not giving me the expected results.
Thanking you in advance for any help

 

[kskid]

hosp_svc in ['SRC','SDS','GNC']

OR

hosp_svc in ("SRC","SDS","GNC"]

 

[kskid]

Typo on the 2nd one. Should be

hosp_svc in ["SRC","SDS","GNC"]

 

[JMB7]

Thank you for your responce. I tried your syntax, but it is still returning patients with any combination of the three. I need all three.

 

[Katy44]

Do you have a table which has a record for each combination of patient and hosp_svc?

 

[JMB7]

The table has one record for each occurence, they are not combined.

 

[Katy44]

OK, try this:

in the details section of the report have patient ID (or equivalent) and hosp_svc.
Create a formula which is 1 if "hosp_svc in ('SRC','SDS','GNC')" else zero.
Put that on the details line too.
Add a group - by Patient ID.
Add a summary - count of the formula field and put that in the group header.
Add a selection criteria - where the summary = 3.
Suppress the details section and anything else you don't want to see.
Does this work?

 

[JMB7]

I am trying your suggestion, just running into problems where some patients might have 4 SDS, then 1 SRC and 1GNC. I will let you know - thank you for your help.

 

[lbass]

Group on patient ID and then create a formula for each value to be placed in the detail section:

//{@SDS}:
if {table.hosp_svc} = "SDS" then 1 else 0

//{@SRC}:
if {table.hosp_svc} = "SRC" then 1 else 0

//{@GNC}:
if {table.hosp_svc} = "GNC" then 1 else 0

Then go to report->edit selection formula->GROUP and enter:

sum({@SDS},{table.patientID}) > 0 and
sum({@SRC},{table.patientID}) > 0 and
sum({@GNC},{table.patientID}) > 0

-LB

 

[JMB7]

Thank you so much for your help. It worked beautifully!