Avoiding Duplicate Records

[Theadmans]

Hi,

I am using CR8.0 with SQL2005 and am writing a report which uses a Transaction Header table which includes multiple records for a single transaction (there is one record for each payment method used on the transaction).

The Transaction Header Table is joined to the Transaction Detail table from which I extract the Quantities sold and Sales Values to sum up to a total.

I only want a single record for each transaction in the Transaction Header file. At the moment the totals I am calculating from the Detail File are being multiplied by the number of records in the Header File for this transction.

I have tried selecting Database->Select Distinct in Crystal but this does not work as I am now missing records from the Detail File.

I thought about using a formula like:-

IF {Header.Txn} = PREVIOUS({Header.Txn} THEN False ELSE True

I inserted this formula (@Include) in the Detail section of the report and then made a formula for the sales total which
said :-

IF (@Include) THEN {Detail.value} ELSE 0

I then SUM the above formula but get Evaluation time problems.

Can any clever person suggest a way round this problem please.

 

[Madawc]

You first need to group, on some value like account number. Insert > Group and choose a field. Note that it has to be some property of an individual 'row', a detail line derived from a single record, or from several individual records from different tables (datasets).

You can then move the record details to group header or footer, and suppress the detail line. (Right-click on the section and choose Format Section.)

If the records are not all identical, you can choose Report > Record Sort Expert to put them into a particular order.

This is based on what I could do in Crystal 8.5, should be OK for 8.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[dgillz]

What you are experiencing is expected behavior. If you do not want to see all the rows, insert a group and suppress the details section.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?