How can I hide duplicate records (within a crystal report) which have already been reversed?

Question

This question is a follow-up to a question I asked the other day ("Need to find duplicate records but exclude reversed transactions").

I have a Crystal Report which displays customers' fuel transactions. There are occasions when a duplicate transaction will be imported erroneously. If/when this error is discovered, the admin will reverse transaction, thus leaving the two original duplicates and a "negative" transaction for the reversal. Example:

Date;     Qty;     Value;     Reversal

1/14/08     5       20         N
1/14/08     5       20         N
1/14/08    -5      -20         Y
1/20/08     8       56         N
1/22/08     9       62         N
1/22/08     9       62         N

Ideally, the customer will only see SINGLE transactions (or a duplicate transaction which has not been reversed yet). If the transaction has been reversed, we only want them to see the original unique record. Example (based on data above):

Date;     Qty;     Value;

1/14/08     5       20
1/20/08     8       56
1/22/08     9       62
1/22/08     9       62

I already have a query which will return the data in the way I just described, but I need to integrate it with a crystal report which requires joins to other tables. Performance is TERRIBLE if I use the sql that I already have, so I'm trying to see if it would be possible to hide duplicate (reversed) transactions within Crystal's formulas instead of using a SQL command as a table joined to other tables.

Please let me know if I am being unclear. I appreciate your wisdom in advance!

Answer 1

Does the reversals always sort to the bottom? If so you can use a formula to check the Next record and conditionally hide the detail section.

So in the detail section add a formula to conditionally suppress the section if it is a reversal record or if the current record matches a subsequent reversal record:

( {Reversal} = "Y" ) OR 
( {Date} = Next({Date}) AND {QTY} = Next({QTY}}) * -1 AND 
  {Value} = Next({Value}) * -1 AND Next({Reversal}) = "Y" )

If the reversals are always matched you could simplify it to:

( {Reversal} = "Y" ) OR 
( Next({Reversal}) = "Y" )

Answer 2

Is it too late to change the data model slightly, so that reversing and reversed records and flagged in some way? I once worked on such a system where records were inserted with a flag of 'C' (current) and if reversed were updated to 'O' (original), and the reversal record was marked as 'R'. All queries that only wanted to see current data could just add to the WHERE clause:

AND rec_type = 'C'