Error Reporting on the first two of three (or more) records in a group 2

[Designware]

Hi,

I have a single table report that is sorted by PO# then Item#. The data looks something like:

Line ---PO ---- Item ----- Qty ----- Cost ---- Ext Cost
1 ------ 1 ------ A -------- 2 ------- 3.20 ------- 6.40
2 ------ 1 ------ A ------- -2 ------- 3.20 ------ -6.40
3 ------ 1 ------ A -------- 2 ------- 3.50 ------- 7.00
4 ------ 2 ------ B -------- 4 ------- 2.15 ------- 8.60
5 ------ 2 ------ B ------- -4 ------- 2.05 ------ -8.20
6 ------ 2 ------ B -------- 4 ------- 2.00 ------- 8.00

This is an error report, so I only want to pull out POs where the original cost in is NOT exactly backed out by the following negative entry. And the 3rd entry I don't care about. So:

Line 1 brings in $6.40 in product for PO 1 Item A
Line 2 backs out $6.40 in product for PO 1 Item A ... line 1 and line 2 match ... all is well so not an error, don't report on this PO
Line 3 ... I don't care
Line 4 brings in $8.60 in product for PO 2 Item B
Line 5 backs out $8.20 in product for PO 2 Item B ... line 4 and line 5 do not match ... list PO 2 on the report as a problem
Line 6 .... I don't care

I am trying to figure out how I can do this with Crystal Reports 10 running on a MS SQL database.

Thank you for your help. I am stumped.

 

[CoSpringsGuy]

suppress details with this code and you will at least get back the one entry that is in error..
(assuming negative entry will ALWAYS be after the entry it is supposed to be correcting)

not ({yourDB.ext Cost} < 0 and {yourDB_.ext Cost} <> abs(previous(yourDB_.ext Cost})))

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[Designware]

CoSpringsGuy,

Thank you for the reply and teaching me about the previous command. Your assumption was correct that the negative entry will ALWAYS be after the entry it is supposed to be correcting. At this point, it is displaying the problem reversals. And actually, that's much better then I thought could be done. If this is all the farther I can take this, I'm great with that.

But, is it possible to display the first transaction and reversal transactions (Lines 4 and 5 in my example) and add only those types of transactions together. So that the report can state how much each PO $ amount is off, and also give a grand total of the mistakes.

Thanks again for your help!

 

[CoSpringsGuy]

not (next({YourDB_.ext Cost}) < 0 and {YourDB_.ext Cost} <> abs(next({YourDB_.ext Cost})))
and
not ({YourDB_.ext Cost} < 0 and {YourDB_.ext Cost} <> abs(previous({YourDB_.ext Cost})))

at the top of the formula editor change exception for NULL to Default value for NULL

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[Designware]

CoSpringsGuy,

Perfect! Thanks!