Hi All,

I'm relatively new to Crystal and have come up against something that is beyond my wit. Here's the situation: I have an accounting and warehousing system that I'm reporting on and the MD wants me to knock up a report that can tell him what orders we're going to be able to successfully meet using the data for sales orders and purchase orders (goods out vs goods in) and taking current stock levels into account.

I have a table that gives me current stock levels for each product we have (which has a unique product code) and then an sales order table and a purchase orders table. Each of these gives me the stock code, the quantity to either be sold or purchased, and the date that it is due out/in.

My plan is to create a report that lists all sales orders ordered by required date, but then I need a calculation to work out the available stock for that product code for the date the order's required for which will be:

Current stock - sum of sales for that product up to the required date + purchase orders up to that date.

Here's the problem. I don't know how to maintain a running total for the quantity ordered field for a specific product code over time.

I've probably described this really badly, so please let me know any more information you require. If someone could help me out with this then it'd make my life a whole lot better.

Cheers,

Spicer
Nottingham, UK.

Hi DBlank,

Many thanks for your response. I'm trying to think of the best way to convey want I mean so I reckon (unless there's a way of attaching images that I haven't figured out) that I'll show the design of the report I'm after and then the data I'm trying to obtain it from.

So, here's how I'm wanting the report to look, I've put extra info on grouping etc in italics to try and add some clarity....

Year (Group 1)
Month (Group 2)
'Order required by date (Sort order)' 'Stock Code' 'Qty Ordered' 'Can we meet order?'

I've got all the grouping, ordering and other data I need sorted apart from that last column. The reason for this is that as all the orders are listed, the part code changes giving something like the results below...

'Order required by date (Sort order)' 'Stock Code' 'Qty Ordered'
23 Mar 09                                          6001234      15
25 Mar 09                                          6001578      25
27 Mar 09                                          6001234      15

The way the data is stored in the DB is that each sales order has a row in a table (called ORD_DETAIL) which holds the information shown above. There is a table for the differing products we sell (called STK_STOCK) containing the stock code, and the amount of that product we have down in the warehouse. There is also a table that is very similar to ORD_DETAIL called POP_DETAIL that contains all the information for purchase orders (the stock that we are buying in to sell on).

What I need to be able to do is work out the amount of a product that we will have in stock at a given point in time (which will be the required by date for the order). To be able to do this, I need to use the current physical stock that I have (stored in the STK_STOCK table), subtract the quantities in orders prior to that date (Qty ordered from ORD_DETAIL) and then add on the amount of that product being bought in (Qty ordered in POP_DETAIL). Here's where it gets beyond me, as I need to be able to do this for any part number (the part number changes on each new row in the detail as per the example given near the top) and for every date listed in the report (the required by date for the order). So, essentially I need a running total of available stock for each part number for each date. At this point my brain starts to hurt as if I create a running total for the quantity field I don't know how to get it to do it for all the different part numbers.

Is this making sense?

I've cut it down a bit, but here's the fields I'm using in the tables

ORD_DETAIL
Stock Code
Qty Ordered
Date Required

POP_DETAIL
Stock Code
Qty Ordered
Date Required

STK_STOCK
Stock Code (used to link to the other two tables)
Qty in Stock

My running total needs to be STK_STOCK.Qty in Stock + POP_DETAIL.Qty Ordered - ORD_DETAIL.Qty Ordered

but this needs to be unique for each stock code, and take the 'required by date' for each row and then carry out the calculation using all sales orders and purchase orders prior to that date. I hope this clarifies a little.

Many thanks for your help.

Spicer

Hi Lockwelle,

I've considered the subreport approach, and agree that a lot of my problems are down to needing to order the report chronologically, and not by the parts themselves. It is certainly the case that we will have multiple orders for the same part number, and this is why I need to ascertain the amount in stock by taking all sales and purchase orders into account prior to the date the sale for that particular detail line has against it and ignoring those after it.

I guess I could create a formula that instead of keeping an on-going count, just calculates the stock at that point in time each time. I'm assuming there's a parameter for 'today's date' in Crystal? I could ask it to calculate:

Current Physical Stock (from STK_STOCK) - (OH_DETAIL.Qty Ordered where Req date < Today's date) + (POP_DETAIL.Qty Ordered where Req date < Today's Date).

This could remove some of the complexity of maintaining an on-going count.

Hmmm... I'll have a little play....

Cheers for your help guys. I'll get there in the end.

Spicer