Displaying item from group only if one type of item is listed 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. Thus I do not want to display claimant # 001 since that claimant has claims other than AWARD. I have tried to use a global variable in a grouping of claim numbers but have not been sucessful.

My unsucessful formula in the claimants group:

//tried WhileReadingRecords and WhilePrintingRecords
WhilePrintingRecords;

stringVar cClaimNo;
BooleanVar lDisplay;

if cClaimNo <> {claimants} then
(
lDisplay := true;
cClaimNo := {claimants};
);

if {type} in ["BID","PROMOTION"] then
(
lDisplay := false;
);

lDisplay;

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

Expected Output:
003 AWARD
003 AWARD

Any help would be greatly appreciated.

Thanks, G

 

[kskid]

I have a similar situation but used a non-Crystal approach by going into Database... Show SQL Query and added the following statement at the end of the sql selection criteria but before order by

...
AND NOT EXISTS
(
SELECT *
FROM CLAIMS C
WHERE C.TYPE <> 'AWARD' AND
 C.CLAIMANT = CLAIMS.CLAIMANT
)

-larry

 

[lbass]

If you might have claimants who have no awards, but have other claims, then you could do the following. First group on {table.claimants}. Then create a formula {@typeval}:

if isnull({table.type}) or
trim({table.type}) = "" then 0 else
if {table.type} = "AWARD" then 1 else
if {table.type} <> "AWARD" then 10000

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

sum({@typeval},{table.claimants}) in 1 to 9999

-LB

 

[DarkCloud2]

Thanks for both of your replies. I actually went with LB's approach since the time to process the report was much quicker.

Thanks Again