2. PHPRunner General questions
  3. Cumulative Sum
This topic is locked

Cumulative Sum

 		3/8/2009 10:27:09 AM
PHPRunner General questions
B
 bozzo author

I need to do a cumulative sum, with the last column updated record-by-record, in a report.

Something like this:

+-------+----+---------+

| stage | km | cum. km |

+-------+----+---------+

| 1 | 5 | 5 |

| 2 | 7 | 12 |

| 3 | 9 | 21 |

| 4 | 5 | 26 |

+-------+----+---------+


You can find many things in MYSQL for doing so, actually it´s just make a self-join, link HERE:
http://www.onlamp.com/pub/a/onlamp/excerpt...ex3.html?page=3
But if I do it, it will vanish all the results from the list, because the "sum" function groups the results, i need something programatically that will detail all the records, with the results.

Does anyone have any solution?? I suspect the code should be place in "custom" on the "Visual Editor", but how?!

I tried something with variables directly in visual editor, but phprunner seems to isolate the variables "per record"....
Thanks for attention.
Sergey Kornilov admin 3/8/2009

Yuri,
this should be pretty straightforward.

  1. Modify SQL query to select km field twice i.e.
    select ...

    km,

    km as cumkm

    from ...
  2. Set 'View as' type of cumkm to Custom and use the following code:
    $_SESSION["cumkm"]+=$value;

    $value=$_SESSION["cumkm"];
  3. Add BeforeProcessList event to reset the value of $_SESSION["cumkm"]
    $_SESSION["cumkm"]=0;
    This is it.
B
 		bozzo author 3/8/2009

Sergey, thank you very much...
If you allow me abuse of your kindness, how can I "reset" the cumkm variable for each group of the report???

Yuri,

this should be pretty straightforward.

  1. Modify SQL query to select km field twice i.e.
    select ...

    km,

    km as cumkm

    from ...
  2. Set 'View as' type of cumkm to Custom and use the following code:
    $_SESSION["cumkm"]+=$value;

    $value=$_SESSION["cumkm"];
  3. Add BeforeProcessList event to reset the value of $_SESSION["cumkm"]
    $_SESSION["cumkm"]=0;
    This is it.
Sergey Kornilov admin 3/9/2009

You can do this using one of events like BeforeRecordProcessed where you have access to all fields. You'll need to save group field value in session variable as well and reset sum once group field value changes.