Stock Replenishment

[fredp1]

Cr 8.5
SQL Server 2000

Hi All,

Can someone give me a pointer on how to approach a problem on a report to show the consumption of consigned stock?
The logic is a bit complicated but here goes.
The expample has 3 items, A,B,C
The customer was given a qty(opening balance) of 100 on 31/08/2004 for each Item.
I want to track month by month how much of the OB was consumed. The customer can over consume the stock (they can source it from another supplier)

Table1
Item, Date, UsedQty, OpenningBalance(31/08/2004)
A, 1/9/2004, 10, 100
A, 20/9/04, 200, 100
A, 1/10/04, 10, 100
B, 1/9/04, 80, 100
B, 1/10/04, 60, 100
C, 1/10/04, 120, 100

For Item A, in Sept, I only need to report 100, as total usage, 210, was greater than the qty supplied.
In Oct, report qty = 0 as fully used in Sept.

For Item B, in Sept, I only need to report 80.
In Oct, report qty = 20 (the remaining balance of the 100)

For Item C, in Sept, report qty = 0.
In Oct, report qty = 100 as total usage, 120, was greater than the qty supplied, 100.
I also have flexability to change the table structure

Thanks for any pointers.
Fred

Cheers
Fred

 

[synapsevampire]

OK, I finally get it.

Where are you displaying this, at each month level of each part?

Since you don't have data for some months, Crystal won't naturally create a month group for you, so you'll want to think this through carefully.

I would create an intelligence data source using an SP I think, as the rules are fairly complex here, and require iterative types of operations.

In Crystal you could emulate this, but you're into arrays and using the PREVIOUS function to determine if a month exists in the data, amongst other nuisances.

The real kicker here is that your opening balance field is wrong, were it to accurately reflect the opening balance of a given month this would be simple.

-k

 

[fredp1]

Hi Kai,

Thanks for your reply
It takes a while to digest... its a bit tricky

Yes it needs to reprt at a Month/Item level.
I'll try using a running total field to see if that can help.



Cheers
Fred

 

[synapsevampire]

An RT won't do the job.

Here's the theory as I see it:

You need to first determine the dates being used as the data may not show all months.

Once that's done, you need to EITHER display whatever months data they do not have (such as in C above) as the opening qty for Sept, with 0 used, OR you need to add the qty used up to the openingbalance qty for those in the first month.

This means maintaining lots of variables.
numbervar Month1;
numbervar Month1;
numbervar Month1;
numbervar OnHand1;
numbervar OnHand2;
numbervar OnHand3;

Then the next month kicks in with the same sort of logic only you base all counts against the current onhand, while checking to see if it's still the same month. Hence loops.

Finally you can have the display of the month1 & OnHand1 etc. in the group footer.

Were it to be in the details you could simplify this.

-k

 

[fredp1]

I should have clarified that the RT was to be in SQL Server.
I created a rt in reverse in SQL which starts of with the Opening balance and totals down.
Then in Crystal I created a
{@Reported Qty}
if {QOH Running Total} >= 0 then {@Transaction Qty}
else
if previous({QOH Running Total}) >0 then {@Transaction Qty}+{QOH Running Total} else 0

I put the "if previous({QOH Running Total}) >0 then {@Transaction Qty}+{QOH Running Total}" to find the first time the RT goes negative(All the consigned stock is consummed) This then allows me to only report the remainig qty.

The table/report (should) look like this now
Item, Date, UsedQty, OpenningBalance(31/08/2004), RT, Reported Qty(In Crystal)
A, 1/9/2004, 10, 100, 90, 10
A, 20/9/04, 200, 100, -110, 90
A, 1/10/04, 10, 100, -120, 0
B, 1/9/04, 80, 100, 20, 80
B, 1/10/04, 60, 100, -40, 20
C, 1/10/04, 120, 100, -20, 100

As I'm writing this I just releasied that I cannot do sub totals, probably because of the previous function.
I'll have to try and emulate the Reported QTY column in SQLServer to make it straight forward in Crystal.

Thanks for your help

Cheers
Fred