# 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.