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/
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProcSPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL
