Crystal Reports Formulas/Arrays

[jchewsmith]

How can I do the following?:


Date Item run qty produced Remaining
1/1/2010 1234 1 500 250
2/10/2011 1234 2 1000 1000
3/1/2012 1234 3 3000 3000

Total on Hand = 4,250

I want to create the remaining column with a formula based on total on hand less qty produced start with the current and working backwards.

 

[CoSpringsGuy]

confusing post... at least to me

where does total on hand come from?
and
repost your sample data with the values you would expect to see in the "remaining" column

_____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection

 

[IanWaterman]

Assuming you are grouping by Item the quantity on hande formula will be

Sum(Remaining, item)

You can then use this in formula in details
eg

@yourformula
Sum(Remaining, item)-remaining

Replace my text with relevant field names or formulae.

As this formula contains a summary you will not be able to do a frther summary on it

Ian

 

[jchewsmith]

My report is grouped by inventory item. Each item can have several receipts but only on quantity on hand for the entire item. I am trying to come up with a formula that will take the total QOH for the item and use it to calculate how much of each receipt is left on hand.

Example. Item #1234 has 6,000 on hand, that item had three receipts as follows: #1 on 1/1/2010 for 2,000, #2 on 2/2/2011 for 4,000 and #3 on 3/3/2012 for 3,000. Of the total receipts (2,000+4,000+3,000) = 9,000. I want my report to show

Receipt #1 2,000 with 0 remaining
Receipt #2 4,000 with 3,000 remaining
Receipt #3 3,000 with 3,000 remaining

I have a static field of 6,000 that is my quanitity on Hand, I want to use that field to calculate how much of each receipt is left, so if I have 6,000 and the last receipt is 3,000 then I know that I have all of receipt 3 left and 3,000 more. So I use that 3,000 to look at receipt #2 and if I received 4,000 I know that all of the 3,000 on hand should apply to receipt #2 and none to receipt #1.

 

[IanWaterman]

Unfortunately what you require is a Running total or variable. These can not be evaluated backwards.

YOu would have to sort receipts in descending order

Receipt #3 3,000 with 3,000 remaining
Receipt #2 4,000 with 3,000 remaining
Receipt #1 2,000 with 0 remaining

@reset// place this in item group header
whileprintingrecords;

global number items:=QOH;

@Eval // place in details

whileprintingrecords;

global number items;
global number remain;

If Receiptfield < items then remain:=items-receiptfield else
remain:=0;

items:=remain;

Ian

 

[jchewsmith]

I am not sure what you mean by place this in item group header and place in details?

Are these formulas or running totals, or?

And Thank you so much for you assistance.

 

[IanWaterman]

They are formula, I assumed you had grouped data by Item.

The formula I have outlined produce a variable which must be reset for each new item, thus the @reset goes in Item group header. YOu can suppress it so that its not visible.

The @Eval goes in the details line and calculates and sets value of the variable as data is printed, hence the whileprintingrecords; command at the beginning of the formula.

Ian