Running Total Formula

[BradCustom]

I need some help with a formula to calculate the number of pieces remaining and when the remaining quantity reaches 0 or less than 0 then I'd like to know the "need date".

Below is how the report is structured.

I have 7 Sub-reports which pass data to the Main report. I believe that I need the passed data from 3 of the Sub-reports. The Sub-reports are listed below.

Sub-InProdQty
Sub-POStatus
Sub-On Hand Qty
Sub-Hardware Demand
Sub-InProductionQty
Sub-SO Del-Job Time
Sub-JobLeadDays

The Sub-Hardware Demand report shows the number of pieces required for a given delivery date and summerizes the total. I've passed data from the Main report to the Sub-report to do a couple of formuals. The data passed is "PassQtyAssy" which is the number of pieces used per Assembly.

Pass Formula:

whileprintingrecords;
shared numbervar QtyPerAssy :={Material_Req.Quantity_Per}

The formulas in Sub-Hardware Demand are listed below:
"DelLineQty"

whileprintingrecords;
shared numbervar QtyPerAssy;

"LineQtyPerAssy"

{@DelLineQty}*{'Schedules_Excel_Download_'.Open Sched Qty}

"VarLineQty"

whileprintingrecords;
numbervar sumline := sumline + {@LineQtyPerAssy}

"display"

whileprintingrecords;
numbervar sumline;

"PassTotalDemand"

whileprintingrecords;
shared numbervar DemandTotal := if isnull (Sum ({'Schedules_Excel_Download_'.Open Sched Qty}, {'Schedules_Excel_Download_'.Part Nbr}))
then 0
else (Sum ({'Schedules_Excel_Download_'.Open Sched Qty}, {'Schedules_Excel_Download_'.Part Nbr}))

This PassTotalDemand is used in a formula on the Main report.


On the Sub-InProdQty report I pass the total In Production to the Main report. Below is the formula:

"PassInProdQty"

whileprintingrecords;
shared numbervar inprodqty := {@InProdQty}

On the Sub-POStatus report I pass the total Remaining Open on Purchase Orders to the Main report. Below is the formula:

"PassTotRemainQty"

whileprintingrecords;
shared numbervar totrem := {@display}

On the Sub-On Hand Qty report I pass the total In Inventory to the Main report. Below is the formula:

"PassInvOnHand"

whileprintingrecords;
shared numbervar InvTotal := if isnull ({Material_Location.On_Hand_Qty})
then 0
else
Sum ({Material_Location.On_Hand_Qty}, {Material_Location.Material})

What I'd like to be able to do is add (total Inventory + Total Remaining Open PO Quantity + In Production Quantity) which would equal the W.I.P Quantity. Then use that total and subtract each "Sub-Hardware Demand" delivery line quantity. When the result is equal to or less than 0 I'd like to know the "Need Date" which is on the Sub-Hardware Demand report.

My question has two parts; 1 would it be better to do this on the "Sub-Hardware Demand" report where the delivery dates reside. 2 How can I accomplish this?

Thanks for your help!!!!
Please let me know if anything isn't clear or you need more information.

 

[Madawc]

If a formula field contains a shared variable as such - as you've done for "display" - then you can add it to something else inside another formula field.

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP

 

[BradCustom]

I've passed the data to the Sub-report but I'm having a brain cramp on one formula.

The passed totals are:
InProdQtyPass
InvTotalPass
PoTotRemPass

I've added these together in the following formula called "WipTotal"

{@InProdQtyPass}+{InvTotalPass}+{PoTotRemPass}

I can calculate the individual line totals with the following formula "InvLineQtyRem"

{@WipTotal}-{LineQtyPerAssy}

What I'm have trouble with is the running total. I haven't been able to have the "WipTotal" decrease by the "LineQtyPerAssy" for each line. For example if "WipTotal" = 100 and I had 10 lines who's quantity were ten; then I whan to show the running total 90, 80, 70...

Just for information the "LineQtyPerAssy" formula is listed below.

{@DelLineQty}*(OpenSchedQty}

The "DelLineQty" is a passed variable from the Main report.

Thanks for all your help!!