How to print only duplicated detail records

[sdonaldson]

I'm using CR 8.5 Developer Edition on Windows 2000.
I have to produce a report which lists transactions from an MS-SQL database where the account number is common and the value is equal and opposite. For example, given the data below...

Record A/C Value
------ --- ------
1 001 50.00
2 002 10.00
3 001 -50.00
4 003 1.00
5 001 -10.00

...I wish to show only records 1, 3, 2, and 5, in that order. The report must not show any record unless there are one or more other records for the same account number with an equal and opposite value.

I have sorted the report so that the data is extracted in account number and absolute value order, but I can't get CR to print/suppress the Detail section in the desired manner.
I have tried to implement a conditional suppression formula, but with only limited success.

Anyone have any ideas how best to achieve this ?

Thanks in advance for your help.
Shaun

 

[lyanch]

The easiest way to do this would be in your SQL, only bringing in the records you want. You can edit the where portion of the Crystal SQL and it will save it.

You will want to do something like

Where (crystals where statements here) AND not (select id from sametable where sametable.AC = table.AC and -(sametable.value)= table.value) is null

Then you will only have records that have an "opposite" to start with. This would probably be a little more efficient in a SP, but you can do it in plain SQL as well. Note, if this is a large dataset this method could be rather timeconsuming. I always try to put my embedded select statements last in the SQL so that if the record is already eliminated, the additional query doesn't get run.

The rest is just formatting the report to get it in the order you want.

Lisa

 

[dgillz]

Are you saying you want the sum of the group by A/C to be zero? If so group by A/C, and in your group selection formula, put the following:

Sum({value},{A/C})=0

This woudl return only 1,3 and 5 as #2 totals to $10 not zero.

please advise if this is not what you want. Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[synapsevampire]

Your 5th value:

5 001 -10.00

Does NOT have an "equal and opposite value" (which I take to mean demonstrates an offsetting transaction for that A/C). So neither 2 nor 5 should display, perhaps you typo'd and meant to put in A/C 2 for number 5?

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Ngolem]

SV is correct about the last value in your example should not your data be for record 5

5 002 -10.00

then your explanation for the report order would make sense.

A few questions are necessary before a solution can be proposed.

1. Are there always absolute "paired" values of opposite sign? Or can we have for the same accountcode values of:
15, -10, -5

2. What if you encounter a pair of values such as 15, -5 for the same accountcode...but nothing else do we suppress both values?

3. Are there always only 2 values/account code? Or in multivalue situations are we to go "hunting for the pairs" and suppress the odd men out?

these issues must addressed before a solution can be proposed. Jim Broadbent

 

[synapsevampire]

ngolem: I think both of the solutoins proposed nailed it.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[sdonaldson]

Thanks for all of your responses.
I will try to implement your suggestions to see if I get the desired results.
To answer your questions...

dgillz:
Yes, I do want the sum of the value of the printed records to be zero, but the sum of records for any given account number may not be zero. I still need to show the transactions that effectively cancel each other out, even if there are other transactions for the same account that have no "match".

synapsevampire:
Doh !
Yes, it's a typo. Sorry. Record 5 should be for A/C 002.

ngolem:
1. Yes, and, er, Yes. I am converting an existing report and so am following its logic which only looks for an equal and opposite value, but given that this is an accounting system there will be occassions on which two or more cheques pay a single invoice, for example.

2. Yes. The report must only show transactions that have an equal and opposite value, and when it shows one transaction it must show the "matching" one too.

3. No there may be multiple values per account number, so the report must "hunt" for the pairs. I ordered the report into account number + abs(value) order and triedusing the next() function but I couldn't quite get it to work. I guess I'm missing something.

Regards,
Shaun

 

[dgillz]

There must be another field there somewhere that ties the records together. Suppose you have the following data:

Record A/C Value
------ --- ------
1 001 50.00
2 001 -50.00
3 001 50.00

Which of the positive 50's should be matched withthe negative 50? If you can find this additional filed in the database, then group by that field and use the group record selection similar to what I mentioned above.

Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[sdonaldson]

Thanks, dgillz.
According to the logic in the old report that I'm trying to replicate, there is no field that ties the records together. All that the old report does is attempt to find a record for the same account number but with an equal and opposite value. If it finds (any) one it prints the pair of records.
The key on the database includes an internal reference number which is automatically incremented each time a new transaction is added, so I guess the "match" is always going to find the first (ie, oldest) record.

Anyway... I have the Crystal report working now by using a combination of a subreport and a couple of shared variables.
Maybe not the most slick method but it works so unless there is a big performance hit when the report is run against live (ie, a larger set of) data, I'll leave it alone.

Thanks for your help.
Shaun