INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

still seeing supressed values in sum

still seeing supressed values in sum

(OP)
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!

RE: still seeing supressed values in sum

Hi,

Sum(@qty)/Count(@qty)

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: still seeing supressed values in sum

(OP)
sorry Skip, what do I do with that?

RE: still seeing supressed values in sum

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: still seeing supressed values in sum

(OP)
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.

RE: still seeing supressed values in sum

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

RE: still seeing supressed values in sum

(OP)
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 :)

RE: still seeing supressed values in sum

pmax, here's an example...
Item	Qty
A	5
A	5
B	3
B	3
B	3
 

The OP wants to see...
Item	Qty
A	5
B	3
 

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: still seeing supressed values in sum

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

RE: still seeing supressed values in sum

(OP)
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 :)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close