Duplicate Records and Running Totals 1

[TimothyP]

My table looks like this:

Type Invoice# Amount
A 123456 $25
A 123456 $25
A 667788 $74
A 885566 $99
B 776677 $44
C 994455 $77
C 789456 $88
D 555345 $91
D 768900 $54
D 444535 $50
E 345678 $88
F 989099 $39
F 989888 $62

As you can see, I can have duplicate records in the table (e.g. Invoice# 123456).

It's not an ODBC connection so I do not have the option to "select distinct records".

I am trying to write a Crystal 8.5 report to group on Type and have a summed Total Amount in the group footer only for Types A, B, and C. I also want to have a Grand Total Amount summing up the total amounts of A, B, and C together. The problem I'm having is all duplicate amounts are included in the summed amount. When I try to add a new group, my running total fields are incorrect.

Any suggestions would be appreciated.

Thanks,
Tim

 

[goodprg]

I think you will have to use manual running total for this.

 

[lbass]

Group on Type and then on Invoice. For your Type group subtotals, create a running total that selects sum of {table.amount}, evaluate on change of group (invoice), reset on change of group (type). Place this in the type group footer. You can suppress this running total in the other footers by right clicking on it->format field->suppress->x+2 and entering:

not({table.type} in ["A","B","C"])

To get the grand total, create another running total, again choosing sum of {table.amount}, evaluate using a formula:

{table.invoice} <> previous({table.invoice}) and
{table.type} in ["A","B","C"]

Reset never.

-LB

 

[TimothyP]

Thanks lbass!
That worked like a champ!

Tim

 

[TimothyP]

The only problem I'm having now is when I run a report for a particular Invoice Type and the all Invoice #'s and amounts are the same.

For example if I had a table that looked like this:

Type Invoice# Amount
A 123456 $25
A 123456 $25
A 123456 $25
A 123456 $25

the "grand total" running total formula is null because {table.invoice} <> previous({table.invoice}) will never occur.

Any suggestions?

 

[lbass]

Sorry, change the evaluation formula to:

(
onfirstrecord or
{table.invoice} <> previous({table.invoice})
) and
{table.type} in ["A","B","C"]

-LB