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.

Q & A Collection

https://www.eclipse.org/forums/index.php/t/1090404/