Sum on duplicate records

[dzavad]

Hi,
I am using cr10 or cr11 and mySql as the db. In my report I have records that is dublicating the same $ number for the some record (Expense_Amount).All I need to do is to sum Expense_Amount on the diferent groups (Facility total, Department, case type groups)only using one $ number discarding all the duplicates on each record.
If I do running total it doesnt work since I cant use to (maximum) and (sum) functions together.
Any idea?

 

[lbass]

Please show some sample data (labeled) that illustrates the issue and also show the expected results.

-LB

 

[dzavad]

Group Three
Record 1 expected result =76,279.29
Detailed section displayes:
76,279.29
76,279.29
0.00
0.00
76,279.29
76,279.29
76,279.29
76,279.29
76,279.29
76,279.29
76,279.29
76,279.29
76,279.29
76,279.29
76,279.29
76,279.29
0.00
0.00

Record 2 expected result =11,095.36
Detailed section displays:
0.00
11,095.36
11,095.36
11,095.36
11,095.36

Record 3 expexted result 6,041.69
Detailed section displays:
6,041.69
0.00
6,041.69
6,041.69
6,041.69
6,041.69
6,041.69
0.00
Group Two total= ? (this is the problem)

I need the sum on the group Two that is based on single value for each record. This is not he duplicates. This is the way it is recorded in the db.

 

[lbass]

IF by "record" you mean instance of Group #3, you could use a variable like this placed in the group #3 header or footer:

whileprintingrecords;
numbervar summax := summax + maximum({table.amt},{table.group3field});

In the Group #2 footer, use a formula like this to display the result:
whileprintingrecords;
numbervar summax;

In the Group #2 header, use a formula like this to reset:
whileprintingrecords;
numbervar summax;
if not inrepeatedgroupheader then
summax := 0;

-LB