I need ideas on the best way to approach an issue with inventory in a report.

My main report basically details open orders for our company, listing them by date, order number, and then line by line it lists each item on the order with quantities. Grouping is by date and then by order number.

Management wants to see inventory stock quantities for each item on the report listed beside the items on the order. I know the opening stock balance for each item. I need to take the opening balance and subtract the order quantities each time the item is ordered on the main report (basically a running total of the stock balance for each item).

The only answer I can come up with is to create a subreport sorted by ITEM instead of by order. When I sort by Item, I can then get a running total of each item which can then be subtracted from the opening balance to get the balance after each order quantity.  This works fine on the subreport, but I don't know how to pass that information to the main report...when I try, the quantities are resetting each time the order number changes.

Is there a better approach to this situation (rather than using a subreport)? I cannot change the sorting on the main report...it has to be sorted by date and by order number.

They want to see a row by row change. By showing the stock being consumed by orders on a day-by-day basis, they can see on which day (in relation to customer orders) that the stock balance will go negative. This is very simple if I can sort by item number, but they want me to maintain the sorting of the original report (date and customer order #). I had the idea that I can use the report sorted by item number as a subreport, but it's not working as I expected.