Change your SQL to something like this:
Select
'P' as TABLETYPE,
po.PART,
po.PURCHASE_ORDER as ORDERNO,
po.DATE_DUE_LINE as ORDERDATE,
po.QTY_ORDER as QTY,
inv.QTY_ONHAND
from V_PO_LINES as po
inner join V_INVENTORY_MSTR as inv on po.PART = inv.PART
Union
Select
'W' as TABLETYPE,
jc.PART,
jc.JOB as OrderNumber,
jc.DATE_DUE as ORDERDATE,
jc.QTY_COMMITTED as QTY,
inv.QTY_ONHAND
from V_JOB_COMMITMENTS
inner join V_INVENTORY_MSTR as inv on po.PART = inv.PART
Then you'll need to do the following:
1. Create the {@QtyForInventory} formula as specified above.
2. Group your report on {command.PART}
3. Create a running total (I'll call it {#Inventory}):
Field to Summarize: {@QtyForInventory}
Evaluate: On Each Record
Reset: On change of group {command.PART}
4. Create a formula: {command.QTY_ONHAND} + {#Inventory}
This will display your running total on the inventory.
-Dell
Edited by hilfy - 28 Jan 2011 at 9:27am
IP Logged
