Edit Selection Formula Group problems 2

[DarkCloud2]

Crystal Reports 8.5
Oracle 9i/ODBC

I have about 75,000 rows of claimant's claims. I am only interested in displaying claimant's who only have award claims and training claims. Thus I do not want to display claimant # 001 since that claimant has claims other than AWARD and TRAINING. I have tried to use a variable in a grouping of claim numbers but have not been successful.

My unsuccessful formula in the “Edit Select Formula Group”:
sum({@setTypeAwd},{claimants}) > 1 and
sum({@setTypeTrn},{claimants}) > 1 and
sum({@setTypeOth},{claimants}) < 1

//@setTypeAwd
numberVar nAward;
if {type} = ‘AWARD’ then
nAward := nAward + 1;
nAward;

//@setTypeTrn
numberVar nTrain;
if {type} = ‘TRAINING’ then
nTrain := nTrain + 1;
nTrain;

//@setTypeOth
numberVar nOther;
if {type} <> ‘AWARD’ and {type} <> ‘TRAINING’ then
nOther := nOther + 1;
nOther;

Example Data:
claimants type
001 BID
001 AWARD
001 PROMOTION
002 BID
002 AWARD
002 PROMOTION
003 AWARD
003 AWARD
003 TRAINING
004 BID
004 TRAINING
004 AWARD
004 PROMOTION

Expected Output:
003 AWARD
003 AWARD
003 TRAINING

Any help would be greatly appreciated.

Thanks, G

 

[lbass]

Must customers have BOTH awards and training? If you mean that customers can have awards and/or training, but cannot have any other types, then group on {table.claimants} and then create a formula {@notawdtr}:

if {table.type} in ["AWARD","TRAINING"] then 0 else 1

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

sum({@notawdtr},{table.claimants}) = 0

This assumes you have no nulls for {table.type}.

-LB

 

[DarkCloud2]

Thanks LB,

Yes, the claimant must have both an award claim and a training claim and no others. Your sample gives me all instances where a claimant has either a award claim only or a training claim only or both an award claim and a training claim only. What I need is for the claimant to have at least one award claim and at least one training claim. I will have the need to do some and/or processing for my next report which will include the requirement that they must also have a bid claim. For example, if the claimant has a bid claim and either an award claim or a training claim and not any others. I do appreciate your help and will try to build on the example that you have provide. Any additional input would be greatly appreciated.

Thanks, Greg

 

[lbass]

For your current issue, try the following. Create a formula {@trandawd}:

if {table.type} = "AWARD" then 1 else
if {table.type} = "TRAINING" then 1000 else 1000000

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

sum({@trandawd},{table.claimants}) in 1001 to 999999 and
remainder(sum({@trandawd},{table.claimants}),1000) <> 0

This ensures that only those groups that have at least one award and one training type (but no others) will be displayed. The only assumptions here are that there are fewer than 1000 awards and 1000 trainings per claimant.

-LB

 

[DarkCloud2]

Excellent, the solution worked beautifully. Thank You very much.

I was able to solve my other problem of Award or Training and bid by the following formula:

if {table.type} in ["AWARD","TRAINING"] then 1 else
if {table.type} = "BID" then 1000 else 1000000