Running Total that Skips/Selects by Part Number

[TheLibrary]

Crystal Reports 9
I have a simple report that I want to add a running calculation for on hand inventory and order shortages:

ShipDate Part No. ShipQty OnHandQty ShortQty
1-10-04 7000-01 10 15 0
1-10-04 8125-99 20 40 0

1-11-04 7000-01 15 5 10
1-11-04 8125-99 35 20 15

The OnHandQty and ShortQty should work like running totals, but are tied specifically to their Part No.'s. The report must be sorted by ShipDate.

Arrays are limited to one-dimension, otherwise this would be no problem.

Suggestions?

 

[nagornyi]

For each part you need running total on, create formula like
if {part_no}='7000-01' then {ship_qty} else 0
then create running totals on these formulae.

 

[TheLibrary]

Good idea, but I have 1000 part numbers.

 

[nagornyi]

But you still need 1000 running totals -- or what?

 

[Madawc]

You could try a subreport in the detail line, showing the orders for that part up until the shipping date. It will slow the report a great deal, so it depends whether you prefer to spend a short time writing a slow-running report or a long time writing a fast one.

Madawc Williams
East Anglia, Great Britain

 

[TheLibrary]

Nagornyi,
Yes, 1000 parts tracking independently.

Madawc,
Speed is not important (overnight batch), could you show me more of how to setup this subreport idea? I've tried subreports and shared variables with no success.

Thanks

 

[nagornyi]

How are you planning to display 1000 running totals? 1000 columns?

 

[lyanch]

I wouldn't try to do this in Crystal unless you have crystal 9 and can add commands. This type of data running total can be easily handled in a stored procedure.

Lisa

 

[TheLibrary]

Nagornyi,
The running total is not to be displayed, it is to be used behine the scenes to furnish data to the updated QtyOnHand, which is one column.

Lyanch,
Have Crystal 9, add commands?, how can I "easily handle in a stored procedure"? Are you refering to use of array vaiables? My attempts have failed. Please share your idea!

Thanks

 

[synapsevampire]

Lisa is correct, or even in this case, a View.

But since you do have CR 9, you could write your own query using a subquery to return the amounts from within Crystal if Views or SP's are a problem.

A big advantage to a View would be that it's reusable.

In either case, how would you "calculate" an on-hand qty?

That should be a known value in any MRP system.

Try posting what values you are calculating, and what is known as this seems like a report grouped by date and then by partno, with sums of ordered qty and on hand qty.

-k

 

[TheLibrary]

Synapsevampire,
Thanks for getting in on this one, your help on other issues I have raised has been most helpful!

I am researching the View option.

The report is for tracking availability of parts for outgoing sales orders. The on-hand qty starts as my current stock (yes, from my MRP).

I need to deplete this qty (on the report) for each occurring sales order, show the updated "on-hand qty" that would be available for the next sales order, and thus indicate when a shortage is going to occur.

Gerry

 

[lyanch]

To get the "amt remaining" set up a subselect in your query that totals up all uses for the current partnumber up to (and including) the current date.

somthing like

Select table.partnumber,
table.salesdate,
(select sum(t2.amount) from table t2 where t2.partnumber = table.partnumber and t2.salesdate <= table.salesdate) as totalusedamount
table.xxx
from table table

I used this method quite a bit for similar reporting (only mine has adds and subtracts).

Lisa
where xxxxxxx

 

[Madawc]

In Crystal 8.5, you find subreports under 'Insert'. They are independent reports that run within the main report, but can be given a value or parameter using Subreport Links under edit. Note that this will place an extra selection criteria in the subreports record selection.

Subreports are most useful in the report header or report footer, where they only get invoked once. Putting them in a detail line means that they get invoked for every single detail line, which is a slow solution and should be the last option when all else has failed.

Madawc Williams
East Anglia, Great Britain

 

[TheLibrary]

Lyanch,
&quot;To get the &quot;amt remaining&quot; set up a subselect in your query that totals up all uses for the current partnumber up to (and including) the current date.&quot;

BullsEye! That's what I am trying to do.

I believe the &quot;subselect in your query&quot; means to include the select table in the AmountRemaining formula?

Specifics to what I have to work with:

F4211.SDLITM Part No. number value, unique per record
F4211.SDPPDJ S/O Date date value, unique per record
F4211.SDUORG S/O Qty number value, unique per record
F41021.LIPQOH Stock On Hand number value, MRP value at time of report run, one value for each Part No. (would be the starting AmountRemaining)

Would I translate your formula as:

Select {F4211.SDLITM},
{F4211.SDPPDJ},
(Select Sum(t2.amount) From table t2 Where t2.partnumber={F4211.SDLITM} and t2.salesdate<={F4211.SDPPDJ}) as totaluseamount
table.xxx
From table table

I underlined the parts I don't understand.

Gerry