Skip to Content
avatar image
Former Member

Stock Running Total

Hi,

Hoping someone can help me....I have an order report which shows all our open orders specific to a customer and date range specified in a cascading parameter. The report is grouped on Part number.

What I want is to be able to see the available stock which is a running total from our current stock and the running total is based on the open qty on each line. To achieve this I created a formula field that is current.stock-open.qty. I then created a running total which is based on the formula I just wrote and that resets on the group.

This appears to be working perfectly when there is 0 stock but when there is stock, the figures produced don't add up - though they are close. See below:

As you can see, the first 3 lines show my issue (within the red box). The totals for these lines should read -117 (so first line is ok), -130 & -131.

Any help you can give would be greatly appreciated.

Thanks, Emily

running total.jpg (146.1 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Oct 22, 2015 at 12:51 AM

    Hi Emily,

    You'd need a manual running total.

    Something like this:

    1) Create a formula with this code and place this on the Details Section:

    shared numbervar avl;

    shared numbervar x := x + 1;

    if x = 1 then

         avl := {Current Stock} - {Open Qty Line}

    Else

         avl := avl + {Open Qty Line};

    2) Create a formula with this code and place this on the Group Header:

    shared numbervar avl := 0;

    shared numbervar x := 0;

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded

    • Glad it works!

      The code subtracts Current Stock from the Open Qty for the first line of each group.

      For all the other lines in the group, it subtracts the value carried forward (from the first line's calcuation) from the Open Qty.

      -Abhilash