still seeing supressed values in sum

[tonyvee1973]

Hi all
Have a report which data has duplicates in that I need supressed. I have a formula (@qty) in the details section and in the section expert area to suppress I have the formula below.

//This formula suppresses the Details line
//when there are duplicate values contained in the fields.
{ORD_DETAIL.OD_STOCK_CODE} = Next({ORD_DETAIL.OD_STOCK_CODE}) And
{ORD_DETAIL.OD_ORDER_NUMBER} = Next({ORD_DETAIL.OD_ORDER_NUMBER}) And
{@f_qty} = Next({@f_qty})

It suppresses the details ok but when I sum on {@qty} it still sums the supressed values.
Sure this is really simple for someone but driving me nuts. I have googled this over an dover but all the answers are so complex. - Help!

 

[SkipVought]

Hi,

Sum(@qty)/Count(@qty)

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[tonyvee1973]

sorry Skip, what do I do with that?

 

[SkipVought]

Rather than simply "sum on {@qty}", Sum({@qty})/Count({@qty}) or whatever CR syntax is correct as I'm not a CR guy.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[tonyvee1973]

Thanks Skip, appreciate the info but cant get it to work.
I place this in a formula and place in the group header but it just divides the qty of the detail line it seems - doesn't sum anything.

 

[pmax9999]

Skip:
Perhaps you could provide more details - I don't understand your approach but would be happy to help turn it into propper CR code.

tonyvee1973
There are a couple of ways I can think of to do this.

I would need to see the formula {@f_qty} to be better able to recommend a solution but essentially one involves the use of a Running Total set to evaluate using a formula, where that formula is the suppression formula quoted in your original post enclosed in parenthesis and with NOT at the start (although the result would need to be in the group footer rather than group header).

A alternaive approach might be to use a string created by concatenating the 3 fields from your suppression formula, grouping on that field so as to create unique records (mover all fields from detail section to group header/footer) and using variables to sum the results of the additional group (again the result would need to be in the group footer rather than group header).

If there is a real need to have the group totals in the header rather than footer, you might need to use the second approach above, but in a sub report.

If you need further help to resolve this, please provide more detail about the report structure (grouping etc) and the code for {@f_qty}.

Cheers
Pete

 

[tonyvee1973]

Ok thanks guys, appreciate the assistance. Will take another look at the report to see if I can find a workaround as maybe its better I get the tables linked better to avoid bringing in duplicates anyway and therefore kill the need for suppression

 

[SkipVought]

pmax, here's an example...
[pre]
Item Qty
A 5
A 5
B 3
B 3
B 3
[/pre]

The OP wants to see...
[pre]
Item Qty
A 5
B 3
[/pre]

But apparently the SUM() he's getting 10 for A and 9 for B.

So what I'd do is either SUM(Qty,@Item).Count(ITEM,@Item): 10/2 = 5; 9/3 = 3. (ie Sum Qty for a specific Item/Count Item for a specific Item)


Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[pmax9999]

SKIP: You may be right, and your approach would work if there was just a single order for each customer. Then again, if that was the case it would be simpler to use the AVERAGE, MINIMUM or MAXIMUM functions, which would all achieve the same result.

Given the OP is going out of his way to identify STOCK_CODE, OD_ORDER_NUMBER and {@f_qty}, I'd guess it isn't that simple.

Perhaps the OP could clarify the situation.

Cheers

Pete

 

[tonyvee1973]

Hi Guys and apologies for the delayed response and appreciate the input. Last night I managed to resolve the issue by using different tables in CR. It seems that the where the tables were linked by order number they were producing duplicate values if the records held multiple invoices per order number. I used different table and linked via invoice number and voila - no duplicates and no need for suppression. I can now sleep soundly