How to Repeatedly Display the Title for Same Group on Each Page
Problem description & analysis
Database table ORDERS has three fields ORDERID, CLIENT and AMOUNT, as shown below:
| ORDERID |
CLIENT |
AMOUNT |
| 1 |
DY |
2000 |
| 2 |
DY |
1500 |
| 3 |
DY |
1000 |
| 4 |
DY |
2500 |
| 5 |
DY |
1700 |
| 6 |
EGH |
1600 |
| 7 |
EGH |
1800 |
| 8 |
EGH |
1200 |
| 9 |
EGH |
1100 |
We are trying to build a report according to a set of rules. Group the table by CLIENT and get ORDERID and AMOUNT columns. The first record in each group is in the form of [the current CLIENT,null], and make sure that the title will repeatedly appear for the same group on each page after page break. When each page contains 3 rows, for instance, the result table after page break is as follows:
Page 1
| ORDERID |
AMOUNT |
| DY |
|
| 1 |
2000 |
| 2 |
1500 |
Page 2
| ORDERID |
AMOUNT |
| DY |
|
| 3 |
1000 |
| 4 |
2500 |
Page 3
| ORDERID |
AMOUNT |
| DY |
|
| 5 |
1700 |
| EGH |
Page 4
| ORDERID |
AMOUNT |
| EGH |
|
| 6 |
1600 |
| 7 |
1800 |
Page 5
| ORDERID |
AMOUNT |
| EGH |
|
| 8 |
1200 |
| 9 |
1100 |
Solution
We write the following script p1.dfx in esProc:
| A |
|
| 1 |
=connect("demo") |
| 2 |
=A1.query@x("SELECT * FROM ORDERS ORDER BY CLIENT") |
| 3 |
=create(ORDERID,AMOUNT) |
| 4 |
>A2.run(if(A3.len()%row==0 || CLIENT!=CLIENT[-1],A3.insert(0,A2.CLIENT,null)),if(A3.len()%row==0 && A3.m(-1).AMOUNT==null,A3.insert(0,A2.CLIENT,null)),A3.insert(0,A2.ORDERID,A2.AMOUNT)) |
| 5 |
return A3 |
Explanation:
Set the script parameter row as 3, which is the number of rows on each page of the report.
A1 Connect to demo database.
A2 Perform SQL, return query result as a table sequence, and automatically close database connection.
A3 Create a table sequence made up of ORDERID field and AMOUNT field.
A4 Calculate expressions on each record of A2’s table sequence. Here run function has three computing expressions. The first two are if functions and the third one is insert function. For the first if function, if the remainder of length of the current A3’s table sequence divided by value of the script parameter row is 0 or if CLIENT value of the current A2’s record is not equal to that of the previous record in A2, add group title to A3’s table sequence. For the second if function, if the remainder of length of the current A3’s table sequence divided by value of the script parameter row is 0 or if AMOUNT value in the last record of the current A3’s table sequence is null, add group title to A3’s table sequence and append ORDERID and AMOUNT in the current A2’s record to A3’s table sequence.
A5 Return result of A3.
Read How to Call an SPL Script in BIRT to learn about the method of integrating the SPL script into BIRT.
https://www.eclipse.org/forums/index.php/t/249387/
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_SPL
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
