Crystal Reports Summing-Grouping Problem

[krazykasper]

Crystal Reports 10.0 - Summing-Grouping Problem

Contract # 12345 has four machines (111, 222, 333, and 444)
Contract # 67890 has two machines (999 and 888)
Each machine has a “base amount” (e.g., Machine 111 has a base amount of 20,000)
Each machine has multiple invoices (some have one invoice others have two, three…ten)
Each invoice shows the “base amount” plus the monthly usage
Invoices do not always have a number assigned
I need to sum the usage and total the base amounts for each Contract
Following is an example of the data for two contracts:
CONTRACT INVOICE MACHINE BASE AMOUNT USAGE

12345 99999 111 20,000 1,000
12345 No Number 111 20,000 500
12345 77777 111 20,000 500
12345 No Number 111 20,000 1,000


12345 66666 222 30,000 200
12345 55555 222 30,000 200
12345 44444 222 30,000 200

12345 No Number 333 20,000 400


12345 No Number 444 100,000 600
12345 No Number 444 100,000 600
12345 33333 444 100,000 1,000
12345 22222 444 100,000 1,000
12345 No Number 444 100,000 600
12345 11111 444 100,000 600

67890 45454 999 10,000 300
67890 No Number 999 10,000 300
67890 No Number 999 10,000 500

67890 32323 888 5,000 200
67890 87878 888 5,000 800

The summed/totaled amounts should look like:
CONTRACT BASE AMOUNT USAGE
12345 170,000 8,400
67890 15,000 2,100

I can sum the Usage for each machine (Group 2 on Machine – SUM Usage); and
use the Base Amount (without summing) which brings me closer to the desired results:
CONTRACT
12345
MACHINE BASE AMOUNT USAGE
111 20,000 3,000
222 30,000 600
333 20,000 400
444 100,000 4,400

67890
999 10,000 1,100
888 5,000 1,000

But when I try to SUM the Base Amount (Group 1 – Contract) I get:
CONTRACT BASE AMOUNT USAGE
12345 790,000 8,400
67890 40,000 2,100

OR if I try to put the Base Amount field into the report (Group 1 – Contract) I get:
CONTRACT BASE AMOUNT USAGE
12345 20,000 8,400
67890 10,000 2,100

I tried use a running total and set it to sum on change of group and reset on the next group higher so it will only sum the field once (kind of like a distinct sum) but this method doesn't seem to work. (Create a Running Total field in Field Explorer Window - Field to Summarize is Base Amount - Type of Summary is SUM - Evaluate "On Change of Group" - Reset "On Change of Group". I put the Running Total field in Group 1 - Contract.

As you can see from the tables above the Base Amount is repeated in each invoice. A running sum at the contract level sums the Base Amount from each invoice. For Contract 12345 in the above example that totals 790,000 when it should be 170,000.

I also tried "Averaging" the Base Amount in Group 2 (Machine) and then doing the Running Total (SUM) on the calculated field but it wouldn't let me select the calculated field.

Is there a way to use a "Conditional" Running Total wherein I could use the "Averaged Base Amount" from Group 2 (Machine)? I know you can use a formula such as {FieldName} >1000 but not sure if I can (or how to) include a "calculated" field.

I also tried calculating the "Average" Base Amount and then putting it into a formula to see if I could SUM the averaged amounts (and then put that field into Group 1 (Contract) but Crystal Reports won't let me calculate the "Sum of Averages". I believe Crystal does not allow you to combine Summary functions.
Following was what I tried in the formula:
SUM Average ({Table1.BaseAmount}, {Table1.Machine})
Also tried
SUM (Average ({Table1.BaseAmount}, {Table1.Machine}))
I appreciate any ideas you may have to help solve this problem.

(I also played around using a Sub-Report for the machine data but that did nothing.)

Thanks,

Krazy (Bill) Kasper

Cross-posted to: http://www.codeguru.com/forum/showthread.php?p=1816926#post1816926