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

Not show suppressed details in sum

Not show suppressed details in sum

(OP)
Hi ALl
Been on this for a day or so now and going around in circles so hope someone can advise?
I have a report where in the detail line I have a formula named "@f_qty" - this holds a formula as below:
if {SL_TRANSACTIONS.ST_TRANTYPE} = 'CRN'
then {ORD_DETAIL.OD_QTYINVD}*-1
else {ORD_DETAIL.OD_QTYINVD}

This returns me values for qty of product sold, however on some of those detail lines there are duplicates.
Not really straight forward as some duplicates are allowed as we sometimes have multiple lines on an order with the same stock code and qty - we would want to return these values.
However, there are some records which are full duplicates that need to be suppressed. These are records which duplicate stock code, qty and location.
The formula below is in section expert within details and suppresses these records ok:
{ORD_DETAIL.OD_STOCK_CODE} = Next({ORD_DETAIL.OD_STOCK_CODE}) And
{ORD_DETAIL.OD_ORDER_NUMBER} = Next({ORD_DETAIL.OD_ORDER_NUMBER}) And
{ORD_DETAIL.OD_LOCATN} = Next ({ORD_DETAIL.OD_LOCATN}) And
{@f_qty} = Next({@f_qty})

Now on to my issue which im sure is a simple one to some but driving me crazy.
I have inserted a summary for the "@f_qty" field but it sums all values even the suppressed ones. I've tried lots of stuff inc running totals etc but just keep making a mess of it, can someone help please?
attachment shows the incorrect sum.


RE: Not show suppressed details in sum

if {ORD_DETAIL.OD_STOCK_CODE} = Next({ORD_DETAIL.OD_STOCK_CODE}) And
{ORD_DETAIL.OD_ORDER_NUMBER} = Next({ORD_DETAIL.OD_ORDER_NUMBER}) And
{ORD_DETAIL.OD_LOCATN} = Next ({ORD_DETAIL.OD_LOCATN}) And
{@f_qty} = Next({@f_qty})
then 0
else
if {SL_TRANSACTIONS.ST_TRANTYPE} = 'CRN'
then {ORD_DETAIL.OD_QTYINVD}*-1
else {ORD_DETAIL.OD_QTYINVD}

RE: Not show suppressed details in sum

(OP)
Thanks Charliy but a bit confused on what I do with that formula?
I tried pasting it into the @f_qty formula but it errors as cannot point to itself
If I try to add as the suppress formula in the details section it errors as needs to be ab Boolean - any ideas?

RE: Not show suppressed details in sum

You set this up as a separate formula, and then you do a sum of that formula.

RE: Not show suppressed details in sum

(OP)
Thanks again for this, however I tried that but no joy.
When I put you're formula in the details section of the report, when I right click and choose "Insert" it only gives me the option of field heading and not summary.

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