Sage 100

Is there a logical way to calculate Qty Short on a custom W/O Material Shortage Report?

You could use IM_ItemWarehouse's qty avail but I think the qty req'd for WO would already be taken into consideration. What are you trying to do?

Basically trying to create the standard W/O Material Shortage Report in Crystal - with additional parameters, sorting, etc.

It appears the issue I'm running into is being able to appropriately factor in qty needs with an earlier due date. For example, if I have a qty need due 6-18-13, I need to factor in the qty need that's due earlier (e.g., 6-3-13).

Each qty short record is not simply relative to what's avail in inventory; it has to take into consideration other commitments. I can do the former on a Crystal report but not sure how/if the latter can be done in Crystal.

Ex. 1: If I need 5 on 6/3, and 6 is available in Inventory, that record is not short - and leaves 1 available in Inventory. So the 3 I need on 6/18 would then be 2 short, not ""not short"" against the 6 initially available in Inventory. Ex. 2: If I need 5 on 6/3, and 4 is available in Inventory, that record is short by 1 - and leaves 0 available in Inventory. So the 3 I need on 6/18 would then be 3 short, not ""not short"" against the 4 initially available in Inventory.

You could create a report that uses the line item receipt date from the PO entry detail table. Ex.1 above: if a PO for qty 5 for the item is expected on 6/15 your adjusted qty available is: beg inv 4 - 5 on 6/3 SO + 5 on 6/15 PO - 3 on 6/18 SO leaves 1.

Doing a date sensitive shortage report is tough without MRP as WO by nature is not really a time phased tool. If they are using MRP it gets easier as you can run MRP and then use the Supply_Demand file for all your data. If not using MRP, seems like you would have to create a running total based on order dates. Begin with current IM on hand and start adding/subtracting PO/SO/WO qtys, but only if date of each is on or before date of your Target WO that you are reporting on. If it is later, ignore it. Pretty sure this may mean a bunch of subreports. By the time you are done, you will probably decide it would have been easier and cheaper to have the customer purchase MRP and let the system do all this figuring for you. I will look around to see if I have a sample from old days.

If youi are doing a bunch of subreports, try using Access and select queries (and of course PTQ's).

Hear, hear, @MikeFitzgerald. Reminds me of trying to create an Inventory Valuation Report by Period. Thanks.

I was going to recommend using a running total but couldn't figure out how to get the Qty on Hand as the beginning amount. I just saw this on the Crystal Reports LinkedIn group: IF OnFirstRecord THEN running_qty_var := Output_Shared_Var + {myTable.qty} ELSE running_qty_var := running_qty_var + {myTable.qty} Totally forgot about the OnFirstRecord concept.