How do I?

[AvgMoJoe]

Crystal 2008
MS SQL DB

Given the following table structure:

Provider.Provider_Name (Group 1)
Prov_Spec.Line Specialty.Name
---------------------------------
Dr A
1 Internal Medicine
2 Hospitalist

Dr B
1 Hospitalist
2 Internal Medicine

Dr C
1 Internal Medicine

Dr D
1 Hospitalist
2 Pediatrics
3 Internal Medicine

Dr E
1 Hospitalist

I need to group physicians to a new type (let's call it ServiceLine). ServiceLine is dependent upon specialty, but if there are multiple specialties, it can get tricky. I'm not sure how to make groupings when I have to compare across results.

If they have Internal Medicine (Specialty_ID 2345) then they belong to ServiceLine="Primary Care" unless they have Hospitalist (Specialty_ID 6789) as a specialty, then they should be in ServiceLine="Inpatient" (and not in "Primary Care"), but if they also have Pediatrics (Specialty_ID 1111) they they should be in both "Inpatient" and "Women and Childrens" ServiceLines.

My result should look like:

Primary Care
Dr C
Inpatient
Dr A
Dr B
Dr D
Dr E
Women and Childrens
Dr D

 

[Madawc]

You're wanting to group by "Specialty.Name": use that for grouping. You could also re-order the record links, but it should work fine either way. Note that detail lines are one line for each combination of the linked records.

You also seem to be using different names for the group. That's not hard, simply remove the automatically generated Group Name and replace it with a Formula Field that substitutes the text you want.

PS It helps everyone if your subject line gives at least some clue as to what you need to know.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[AvgMoJoe]

I don't believe you understood the nuances involved. Specialties could belong to one or more service lines, and the grouping will be dependent on how specialties compare among themselves for one physician.

 

[IanWaterman]

You will need to create group formula for Primary Care, Inpatient, Women and Childrens and sort alphabetically ascending, as Primary Care will be dependent on Inpatient, inpatient must be evaluated first

You will need to populate a global array with names of doctors meeting inpatient category.

Then in the Primary Care group conditionally suppress Dr if their name appears in the array.

Ian