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

 

Is there some way to “sum” the amounts shown in Group #2 (Machine) into Group 1 (Contract)?

Or some other way to achieve the desired results?

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

 

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.

 

If I'm understanding you correctly, I'd triple check that you're evaluating the running total "On Change of Group" for the Machine group.  The numbers you're giving look to me like they're evaluating for each record.  If it's set to evaluate on change of machine group, the only other thing I can think of is to set a global variable to act as a running total.  You can then specify the

 

Global NumberVar RunTotal := 0;

In the Contract group header to reset the variable, then

WhilePrintingRecords;
If RecordNumber = 1 or ({Table1.Machine} <> Previous({Table1.Machine}))
    Then NumberVar RunTotal := {Table1.BaseAmount}
Else RunTotal := RunTotal;
RunTotal

In the detail section and contract group footer.

 

Hope this helps, or at least points you in the right direction.

If needed, I'll try using the global variable.