help with whileprintingrecords 1

[up4a]

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.

 

[lbass]

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

 

[up4a]

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!