Stock Consumption help

[fredp1]

SQL Server, CR 8.5

I'm trying to put a report together which shows the consumption of stock within a site,calander period, item.
The report needs to show in which month the stock was fully consumed.
If the stock is over consumed, I want to show the QTY for 31-AUG-2004
The starting Qty is the qty on the 31-Aug-2004

The Input table is
Site,Item, datetime, qty
Syd, A, 31-Aug-2004, 1000 <=== Opening balance always on this Date
Syd, A, 01-Sep-2004, -500
Syd, A, 23-Sep-2004, -400
Syd, B, 31-Aug-2004, 1000 <=== Opening balance always on this Date
Syd, B, 01-Sep-2004, -500
Syd, B, 23-Sep-2004, -900
Syd, B, 05-OCT-2004, -500
Syd, C, 31-Aug-2004, 1000 <=== Opening balance always on this Date
Syd, C, 01-Sep-2004, -500
Syd, C, 23-Sep-2004, -400
Syd, C, 01-OCT-2004, -500

For Sept,
Item A is not shown (only consumed 900/1000)
Item B is show, QTY consumed is 1000(OB at 31/08/2004, not 500+900+500)
Item C is not Shown (only consumed 900/1000)

For October
Item A is not shown as it isn't fully consumed.
Item B is not shown as it was consummed in the previous month
Item C is shown, Qty consumed is 1000(OB at 31/08/2004)

I'm also needing to do some grouping by Site, date and item, to get some subtotals (the qty consumed ).
I've tried some prototyping on the report using running totals, but I'm finding I cannot do subtotals with running totals.

I have a lot of flexibility on changing the input tables, so any suggestions would be appreciated. e.g. i can put the Openning balance figure in a seperate table to the transaction data and do a join

I hope thats clear

Thanks
Fred

 

[fredp1]

After sleeping on the problem, I think the trick thats needed is to determine the first date the item gets consumed.
When this date is applied for every record for that Site/Item, then I can group on this date.

The only way forward that I can see is to do the calc in SQL Server. This will them make the Crystal Report straight forward.

The new table layout will then look something like this

Site, Item, TranDate, Qty, FirstDateConsumed
Syd, A, 31-Aug-2004, 1000, <null>
Syd, A, 01-Sep-2004, -500, <null>
Syd, A, 23-Sep-2004, -400, <null>
Syd, B, 31-Aug-2004, 1000, 23-SEP-2004
Syd, B, 01-Sep-2004, -500, 23-SEP-2004
Syd, B, 23-Sep-2004, -900, 23-SEP-2004
Syd, B, 05-OCT-2004, -500, 23-SEP-2004
Syd, C, 31-Aug-2004, 1000, 01-OCT-2004
Syd, C, 01-Sep-2004, -500, 01-OCT-2004
Syd, C, 23-Sep-2004, -400, 01-OCT-2004
Syd, C, 01-OCT-2004, -500, 01-OCT-2004

If anyone has any suggestions on how this could be done using straight Crystal, I'd be interested to try it out.

Thanks for listing
Fred

 

[synapsevampire]

If you're grouped on the item, and you do a sum of the qty, then it seems that you know what needs to be used.

Report->Edit Selection Formula->Group

sum({table.qty},{table.item}) <= 0

Or you can use the above in a formula to suppress data if you still want to show it.

This should get you close.

-k

 

[fredp1]

I ended up getting this working by getting the data massaged in SQL Server.


Cheers
Fred

 

[synapsevampire]

Always better to let the database do the heavy lifting, but it's not so difficult in CR either.

-k