How to do a Sum of non duplicate records 2

[Twain35]

Hi,
I have a report that is summing duplicates. The formula I am using is:

@sum
whileprintingrecords;
numbervar SUM;
SUM := SUM + {table.field}
SUM;

This is summing but is also summing duplicates. Does anyone know of a formula that would sum only distinct values? Your help will be appreciated. Thanks.

 

[IanWaterman]

Sort the records on a field which is also duplicated.

Then use a Running Total, in the evaluate section select on change of the field above which is duplicated.

Leave rset as never, RT will now return sum of items excluding duplicates.

Ian

 

[lbass]

To adapt your manual running total, change it to the following, using the duplicating field in the string variable (assuming it is a string):

whileprintingrecords;
numbervar x;
stringvar y;
if instr(y,{table.string}) = 0 then (
y := y + {table.string};
x := x + {table.field}+","
);
x;

But Ian is correct, using an inserted running total is probably easier and you wouldn't run into a potential formula error saying the string length is too long.

-LB

 

[FireGeek21]

I have used the 3 formulas technique for summing (group header, detail and footer where the footer is the only one not suppressed). In the detail I have used, If NOT InRepeatedGroupHeader. LBass, would the NOT InRepeatedGroupHeader work in this instance - sum unique values?

-FireGeek

FireGeek
(currently using Crystal Reports XI with Lawson 8.03)

 

[Twain35]

Thank you lbass and Ian. I forgot to mention that I am also using a crosstab report and want to see the totals there but it seems like the last total values add up to a very high number. There is a field {table.area} that range from 1-8, so I put that in the row and summarized by {table.structure}.
Let me break it down. I have two views (view1 & view2) that are linked by a left outer join which have common fields. The summing work fine with view1. Now I have to add view2 because there is one field that needs to be put in the report. So, with the view1 and view2 linked, the summing values are now duplicating, which is the result of a cartesian product I believe. So I have used a runnig total to get the summing right in the detail section, but when I put the RT in the crosstab, the totals are higher for each (area 1-8 off of {table.area}) than what I see in the detail section. So now, how can I get the crosstab to show exactly what I see off the RT in the detail section of the report? e.g

this is what i want to see in the crosstab

Area structure
1 10089
2 34899
...8 300000

but this is what im getting

Area structure
1 39090902
2 289898900
..8 230000000

Thanks.

 

[lbass]

Please explain exactly how you have the running total set up, identifying specific field names. The also identify the field names used in the crosstab for row and column.

-LB

 

[Twain35]

This is how the running total is set up.

for the running totals, i just want to do a summary of the {table.structure} and in the evaluate I have inserted the {table.structure} in the "change of field" and left the reset to "never". When I put this running total in the detail section, the summaries come out ok with respect to the increasing increments of the area. e.g

report detail section:

Area RT building count
1 10000 5
2 23000 7
3 45980 3
4 70009 12
5 6
6 2
7 4
8 100000 16
200000 55


for the crosstab:
I am using three fields
1. {table.area} - row : This field will display area numbers from 1 to 8 vertically.

2. distinct count of {table.buildingID} - in the summarized box : this will count the number of buildings for each area.

3. summary of {table.structure} - in the summarized box: this will calculate the summary for each area.


Now when I insert the running total abve for the crosstab in the summarized box i get the following values :

crosstab :

Area Building count RT

1 5 389000
2 7 590000
3 3 790303
4 12 1000899
5 6 3400008
6 2
7 4
8 16 1299999
Total 55 230546445



I want to see the Total to be 200000 for the RT in the crosstab, not 230546445. I've been playing around with the evaluate and the reset to get the summarized value at the Total to be 200000, but just can't seem to find any field or formula to show that. So any help with this will be very much appreciated.

 

[lbass]

I'm not understanding how you have the rt set up. What field are summarizing? How does the field display in the detail section in the detail section? What kind of summary you are using?

-LB