Adding Unique Amount of Invoice

[kutoose]

I am using Crystal Reports 10, SQL Server

I need to add the invoice amount for unique invoice in a group. The report is grouped
by Purchase Order creator, PO Order Number - PO LIne and Invoice ID. In the detail section
I have PO line information

An invoice can appear under more than one PO Number - PO LIne. In that case only
one instance of Invoice ( Invoice Amount) should be counted. How can this be accomplished ?

Group 1 - PO Creater - Last Name, First Name

Group 2 PO - PO Line - 11111-01
Group 3 Invoice ID

Invoice ID Invoice Amount
100 $ 200
200 $ 50
300 $ 1000
Group 2 Total $ 1250

Group 2 PO - PO Line - 11111-02
Group 3 Invoice ID

Invoice ID Invoice Amount
100 $ 200
400 $ 50
500 $ 250

Group 2 Total $ 500

Group 2 PO - PO Line - 11111-03
Group 3 Invoice ID

Invoice ID Invoice Amount
600 $ 100
500 $ 250

Group 2 Total $ 350

Group 1 Total $ 1650

If I add all the instances of invoice ids then I get $ 2100. The amount I need is $ 1650. How can I do this in Crystal ?

 

[Madawc]

Do a subreport, in which you sort the same data by Invoice ID and ignore duplicates - this is an option for Running Totals. Subreports are inefficient, but if you just put it in the report footer for a grand total, that should be OK.

Madawc Williams (East Anglia, UK)

 

[kutoose]

Subreports were giving me performance issues( I am not displaying the info in report footer ). Is there a formula or some thing which will be of help ?

 

[lbass]

If you are only concerned with the group #1 total, then you could set this up with variables, as follows:

//{@reset} to be placed in the Group #1 header:
whileprintingrecords;
numbervar x := 0;
stringvar y := "";

//{@accum} to be placed in the detail section:
whileprintingrecords;
numbervar x;
stringvar y;
if instr(y, totext({table.invoiceID},"000")) = 0 then
(y := y + totext({table.invoiceID},"000") + ", ";
x := x + {table.invamt});

//{@display} to be placed in the Group #1 footer:
whileprintingrecords;
numbervar x;

-LB