## 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.

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

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