Last YTD + This YTD

[blueboyz]

I am using Quantum 2014 and Crystal Reports 2008. Pervasive SQL is the database engine.

I created a report to display Available Inventory as of today's date.

The user is required to enter the inventory item id's in a parameter and the year in another parameter.
My record selection is:

If {?Item ID} = "ALL" then TRUE else {LineItem.ItemID} = {?Item ID} and
{JrnlHdr.JrnlKey_Journal} in [10, 11] and
{JrnlHdr.POSOisClosed} <> 1

The Journal = 10 is the Purchase Orders.
The Journal = 11 is the Sales Orders.

I created formulas for each period to calculate the quantity on open Purchase Orders and the quantity on open Sales Orders:
{@PO P-1} = If {JrnlHdr.JrnlKey_Journal} = 10 and (month({JrnlHdr.GoodThruDate}) = 1 and year({JrnlHdr.GoodThruDate}) = {?YEAR}) then ({JrnlRow.Quantity} - {JrnlRow.QtyReceived})

{@SO P-1} = If {JrnlHdr.JrnlKey_Journal} = 11 and month({JrnlHdr.ShipByDate}) = 1 and year({JrnlHdr.ShipByDate}) = {?YEAR} then ({JrnlRow.Quantity} - {JrnlRow.QtyReceived})

To get the available inventory, the {@OnHand} is added to the purchase orders quantity and the sales order quantity is subtracted {@P-1 AVAIL}:

{@P-1 Avail} = {@On Hand} + Sum ({@PO P-1}, {LineItem.ItemID}) - Sum ({@SO P-1}, {LineItem.ItemID})
"P-1" stands for period 1.

For the second period, the available inventory is {@P-2 AVAIL}:

{@P-1 AVAIL} + Sum ({@PO P-2}, {LineItem.ItemID}) - Sum ({@SO P-2}, {LineItem.ItemID})

My Data looks like:
Item ID On Hand Jan Feb ...... Dec
A 8 87 0 0 Sales Orders
27 80 0 Purchase Orders
-52 28 28 Available

In the example above, Jan = On Hand qty + Jan Purchase Order qty - Jan Sales Order qty:
{@On Hand} + Sum ({@PO P-1}, {LineItem.ItemID}) - Sum ({@SO P-1}, {LineItem.ItemID})
Feb= Jan Available + Feb Purchase Order qty - Feb Sales Order qty
{@P-1 AVAIL} + Sum ({@PO P-2}, {LineItem.ItemID}) - Sum ({@SO P-2}, {LineItem.ItemID})
Mar= Feb Available + Mar Purchase Order qty - Mar Sales Order qty
{@P-2 AVAIL} + Sum ({@PO P-3}, {LineItem.ItemID}) - Sum ({@SO P-3}, {LineItem.ItemID})

The report works great until the next year. The data displayed is only for the year the user puts into the parameter. For January of the next year, I need the report to take the Dec Available + Jan Purchase Order qty - Jan Sales Order qty. I suppose I many need another parameter to show a date range but I'm not sure how to get last December quantities to add to this year's quantities for Jan.

Any help is greatly appreciated.

 

[pmax9999]

This is difficult to understand without being familiar with your data, but the problem seems to be that both {@PO P-1} and {@SO P-1} formulas are only testing for a Year() = {?YEAR}. Those two formulas need to cover the situations for subsequent years with an else statement, eg:

[Code {@PO_P-1}]
If {JrnlHdr.JrnlKey_Journal} = 10 and
month({JrnlHdr.GoodThruDate}) = 1 and
Year({JrnlHdr.GoodThruDate}) = {?YEAR}
Then ({JrnlRow.Quantity} - {JrnlRow.QtyReceived})
Else
If {JrnlHdr.JrnlKey_Journal} = 10 and
month({JrnlHdr.GoodThruDate}) = 1 and
Year({JrnlHdr.GoodThruDate}) = {?YEAR} + 1
Then .............
[/Code]

Hope this helps.

Cheers
Pete