Record Exclusion

[Johnblaze1]

I am in need of help limiting records coming to my report.
I am using version 8.5

This is a sample recordset for illustration. There are 2 tables, Person, and Sports

Person would have a numerical ID for each person.
Sports has the person ID and a sport name, forming a one to many relationship

PERSON.ID SPORTS.ID SPORT.NAME
1 1 FOOTBALL
1 1 BASEBALL
2 2 FOOTBALL

What I need to do is have anyone who played BASEBALL not come to the report at all. So, in this example, person.ID 1 would not show

I know how to use the group selection to limit showing the data, however, that effects totals and I need to not have this person come over at all.

Any help would be greatly appreciated

Thanks

 

[dgillz]

Go to report, edit selection formula, record and write a formula:

{SPORT.NAME} <>"Baseball"

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[Johnblaze1]

dgilz

Wouldnt that still return ID 1 because he is in football? I need to completely have that person off of the report.

 

[kskid]

I think a twist to one of LBass' formula should do the trick.

First group on {Person.ID}. Then create a formula {@sport}:

if {sport.name} = "BASEBALL" then 1 else 0

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

sum({@SPORT},{PERSON.ID}) = 0

-LW

 

[lbass]

You can use group selection as kskid describes and then use running totals instead of inserted summaries to calculate totals.

Another method uses a SQL expression {%baseball}:

(select max(AKA.`sportname`) from Sports AKA where
AKA.`ID` = Sports.`ID` and
AKA.`sportname` = 'Baseball')

Make sure the expression uses your exact table and field names.

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

isnull({%baseball})

This assumes you have a left join from {Person.ID} to {Sports.ID}. Using this method, you should be able to use the usual inserted summaries.

-LB

 

[Johnblaze1]

Thank you both for your replies
kskid - I am getting an error 'formula must be evaluated later'

lbass, I will try your method.

Thank you both

 

[lbass]

Regarding kskid's suggestion, be sure you are placing the formula in the GROUP selection area, not the record selection area.

-LB