Sage 100

Crystal Report - Count Number of Lines From Sales Order With Qty On Hand. I am trying to get the total number of lines from open sales order with a Qty On Hand. I created the below formula and tried Summary and Running Total to no avail. The Summary adds the Qty On Hand Amounts. I tried to repeat the attached previous solution by substituting @CountOnTime with @Items With Stock and it counts the total quantity on hand instead of the number of lines with a quantity on hand. I have this solution in place on the same report counting the total number of sales order and it works. Qty to Ship formula = {SO_SalesOrderDetail.QuantityOrdered}-{SO_SalesOrderDetail.QuantityShipped} Items With Stock If {IM_ItemWarehouse.QuantityOnHand}-{@Qty to Ship} >0 Then 1 @ChrisStAmand

If your report groups by sales order number then, instead of using a running total, sum the values in the ""ItemsWithStock"" detail field (if {IM_ItemWarehouse.QuantityOnHand}-{@QtyToShip}>0 then 1 else 0). I am not sure by your post if the user wants to count the line (ItemsWithStock) if the quantity to ship = 0. In my example, all lines were counted as long as the qty on hand exceeded the quantity to ship even if the qty to ship =0.

I can get the total number of lines by sales order as you suggested, however I'm trying to get a report total. When I sum the values of ""ItemsWithStock"" in Report Footer is adds the quantity on hand vs. the number of sales order lines that have a Qty On Hand.

Attached is the Crystal Report

You can use the grand total sum of the item with stock column in your summary at the bottom instead of the running total (see the screen shot). The reason the running total isn't summing correctly is because it is including the records that are suppressed on the detail line suppression formula. You could modify the running total formula and use a similar formula so the suppressed detail records are not included in the running total.

When I export to excel the grand total sum is not correct. See Attached.