Unfortunately, the solution depends a great deal on the exact nature of the data.  Is there anything in the record of the reversal to tell that it is a reversal, and not simply a standard withdrawal?  Is there anything that references the original transaction, so that you can know for certain which transaction is being reversed?  Does the reversal always come immediately after the original transaction?

OK, so the only negative amounts are reversals.  That helps.  Will the reversal and the original transaction always have the same claim number?  That should allow us to narrow it down, I think.  It will still be very tricky.

Ideally, I would like to solve this on the SQL side.  You are going to run into a lot of timing problems in Crystal.  Crystal processes each record, in order.  Then, it goes through and processes each record a second time, to allow records to interact.  Essentially, what you want Crystal to do is to go through and, if it encounters a negative amount, to back up and find and flag a previous record.

If you are able to do this in SQL, your query would look something like:

SELECT MemberNo, MyTable.ClaimNo, ClaimDate, ProcessDate, MyTable.Amount, 
    (CASE WHEN (RevCheck.ClaimNo IS NOT NULL) OR (MyTable.Amount < 0)  THEN -1 ELSE 0 END) AS RevExist
FROM MyTable
LEFT JOIN
    (SELECT ClaimNo, Amount
     FROM MyTable
     WHERE Amount < 0) RevCheck
ON MyTable.ClaimNo = RevCheck.ClaimNo AND MyTable.Amount = ABS(RevCheck.Amount)

To walk you through a bit, the CASE statement creates a flag, that is 0 normally, but -1 when a reversal exists for that transaction.  The RevCheck subquery just looks for all the negative amounts.  The join matches up the negative amounts from RevCheck with records that have the same claim number, and the same amount.


If this kind of SQL is not available to you, then you're going to have to use a pretty brute-force method.  I strongly recommend using a forward-looking approach on each record, rather than a backwards-looking approach only on the negative records.  Essentially, create a SQL Expression that looks ahead for reversals.  If it finds one, flag the current record.  Also flag any records with negative amounts.

Once you have your flags in place, by either method, simply set a suppress condition on the flag.

Incidentally, this could be done much more simply if you are able to group by claim number, and sort within each group by amount rather than date.  But, it sounds like that likely isn't an option for you.

I could not get the above solution to work. I however did figure out another way to do it.