Exclude group based on specific Criteria

[Swetal2004]

Hello:
I have a table set up as below:
Pt_id Proc_cd Priority_cd Specialty
10001 74.1 01 OB
10001 75.6 02 FAMIL
10001 66.2 03 PERIN
10001 31.7 04 FAMIL
10001 52.8 05 PERIN
10002 66.1 01 CARD
10003 21.2 01 INTER
10004 11.4 01 INTER
10004 52.6 01 PERIN

I have Crystal 8.5. Based on Specialty I would like to exclude every occourance of that pt_id without grouping. Is there a way to acheive this by writing a formula or writing a code in 'Edit Selection Formula'.

Thanks

 

[Turkbear]

Hi,
Not sure what you mean by:
Based on Specialty I would like to exclude every occourance of that pt_id , especially the
Based on Specialty part...

Can you explain how the Specialty to be excluded will be determined?
Once that is known, it should be relatively simple to write a selection criteria formula to do it.






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Swetal2004]

Sorry about not being very Clear.

From the table above.
If Specialty <> PERIN then all the occourance of pt_id 10001 and 10004 should be excluded from the report. Therefore on the report I should see the following.

Pt_id Proc_cd Priority_cd Specialty
10002 66.1 01 CARD
10003 21.2 01 INTER

Thanks

 

[Madawc]

That's tricky - you want the members of the group to be selected on the basic of a property belonging to the group as a whole. If the data was

Pt_id Proc_cd Priority_cd Specialty
10001 74.1 01 OB
10001 75.6 02 FAMIL
10001 31.7 04 FAMIL
10002 66.1 01 CARD
10003 21.2 01 INTER
10004 11.4 01 INTER

you'd want to see them all, right?

With the data you show, you could do it using a Maximum summary total for the group, which would be PERIN or something else. On this basis you could suppress 10001 and 10004 on the basis of a suppression formula in the detail line.

If there's a valid value greater than PERIN, that won't work. You might need to use subreports to get the output you want.

Madawc Williams (East Anglia, UK)

 

[Turkbear]

Hi,
OK, progress..Now, how are you 'telling' the report which one(s) to exclude..Are you passing a parameter?

If so,then in your selection criteria formula, test the value of the parameter and take appropriate action ( Something like):
If {?Excludethis} = 'PERIN' then
Not (Pt_Id in ['10002','10003'])
...







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Swetal2004]

Madawc Williams, Turkbear
Thanks for your quick response.

I am bringing around 35000 records in the report based on my creteria and therefore I cannot hard code pt_id.

I am trying to write a code or use parameter field so that it excludes all occourance of pt_id with Specialty of PERIN.

 

[Madawc]

If you just want to exclude all occourance of pt_id with Specialty of PERIN, you could do that in record selection. But I understood that you wanted to exclude other records if the group included at least one PERIN.

One possible method is an 'alias', the same table again, treated as if it were different. You could do a left-outer link based on pt-id, with a record-selection test if isnull({alias.OB} or {alias.OB} = "PERIN". Then suppress all detail-line 'rows' where {alias.OB} = "PERIN"

Madawc Williams (East Anglia, UK)

 

[Swetal2004]

Thanks Madawc
Let me try this and I will write you back

 

[Swetal2004]

I am trying exclude other records if the group included at least one PERIN from the data set I bring in the report.

Sorry if I missed to mention that before.

Thanks

 

[lbass]

Insert a group on {table.pt_id} (you can suppress the group header and footer if you like). Then create a formula {@Perin}:

if {table.specialty} = "PERIN" then 1 else 0

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

sum({@Perin},{table.pt_id}) = 0

-LB

 

[Swetal2004]

Thanks
I was hoping if there was a way to do this without grouping or keeping it from being the part of the record set.

 

[lbass]

Can you explain why you don't want to group and what you mean by "keeping it from being the part of the record set"?
I don't see any reason not to group from your example, but I suppose another alternative might be to use a SQL expression. Is that an option with your particular datasource?

-LB

 

[Turkbear]

Hi,
Somehow you need to send something like this record selection code to the database:

where PI_ID NOT IN ( select PT_ID from TABLE where Specialty = 'PERIN')

Maybe you can create/add the Where clause directly in the
Show Sql Query window ( under Database)





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

You could create a SQL expression like {%Perin}:

(select max(AKA.`specialty`) from Table AKA where
AKA.`Pt_ID` = Table.`Pt_ID` and
AKA.`specialty` = 'PERIN')

You would substitute your actual table name for "Table" and your field names for "Pt_ID" and "specialty" if these are incorrect. Leave AKA as is, since it represents an alias table name.

Then in the record selection formula, use:

isnull({%Perin})

-LB