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

help with whileprintingrecords

help with whileprintingrecords

(OP)
Crystal 2011, SAGE database - Providex

Two tables, SO_header and SO_detail linked on SalesOrderNo

I have a report that selects sales orders based on the ship date [so_header.shipdate]< CurrentDate+20, and order type (so_header.ordertype)in ["B", "S"].

It groups on Grp 1, ship date and Grp 2 order number. It then has 2 formulas that return the qty ordered of specific part numbers.
@Labor=if {SO_SalesOrderDetail.ItemCode}="98000" then {SO_SalesOrderDetail.QuantityOrdered} else 0 and
@PnlQty=if {SO_SalesOrderDetail.ItemCode}="11000" then {SO_SalesOrderDetail.QuantityOrdered} else 0
we are also reporting the extended sales amount for each line (So_Detail.ExtensionAmt)

Details are hidden.

These formulas are then summed by sales order and ship date.

I need to suppress any sales orders that the @pnlQty=0 for a sales order. I was able to do this in the report with the group record selection,
Sum ({@PnlQty}, {SO_SalesOrderHeader.SalesOrderNo})<>0

but of course the summaries of the ship date is still including all orders regardless of pnlqty.

To top it off we are then calculating a capacity % based on the ship date totals,

Sum ({@Pnlqty}, {SO_SalesOrderHeader.ShipExpireDate}, "daily")/80*100

I am pretty sure this can be accomplished with a whileprintingrecords in one of the groups but for the life of me I can't figure out where to start. I have used this before with good results.

Thank you so much for your time to look at this and assist.


RE: help with whileprintingrecords

Try using the group selection formula you mention and then create running totals to perform the sums of your conditional formulas. Create a set for each group level. You shouldn't have to add any special conditions to the running totals, just have them reset on change of the group level you are calculating. Then use the running totals in formulas that calculate the percents.

Another alternative would be to use SQL expression to eliminate records where the sum is zero. Something like {%sumqty}:

(
Select sum(quantity ordered)
From salesorderdetail A, SalesOrderHeader B
Where A.salesorderno=B.salesorderno and
A.itemcode='11000' and
B.salesorderno=SalesOrderHeader.salesorderno
)

Then add a record selection formula like this:

{%sumqty}<>0

The particular syntax of the SQL depends upon your version of CR, your driver, etc.

-LB

RE: help with whileprintingrecords

(OP)
Now why didn't I think of that!! Running totals worked perfect. Sometimes I just need to get out of my own way! Thanks again LB!

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