How to Calculate Cumulative Sums
Problem description & analysis
We have a database table SAMPLE. Its data is as follows:
DATE |
VALUE |
2000-01-01 |
1 |
2000-01-02 |
2 |
2000-01-03 |
3 |
2000-01-04 |
4 |
We are trying to add a new column CUM to calculate the cumulative values VALUE values, as shown below:
DATE |
VALUE |
CUM |
2000-01-01 |
1 |
1 |
2000-01-02 |
2 |
3 |
2000-01-03 |
3 |
6 |
2000-01-04 |
4 |
10 |
Solution
Write the following script p1.dfx in esProc:
A |
|
1 |
=connect("demo") |
2 |
=A1.query@x("SELECT * FROM SAMPLE") |
3 |
=A2.derive(VALUE+CUM[-1]:CUM) |
Explanation:
A1 Connect to the database named demo.
A2 Return the query result as a table sequence and auto-close the database connection when the query is finished.
A3 Add a calculated column CUM to calculate the cumulative values VALUE values.
Refer to How to Call an SPL Script in BIRT to learn about the method of integrating the SPL script with BIRT.
https://www.eclipse.org/forums/index.php/t/1090404/