RaqForum 28 No.
544 View  • 
     ...

Dynamically Display Columns with Page Break

 
SQL(179)

【Question】

My question is: How can I prepare template which will receive various number of columns in such way, that if they won’t fit to page, extra columns would be printed on a second page. For example

If I have 10 columns, but only 6 fit to the page 1, the rest would be displayed on page 2. Same functionality is available in Microsoft Excel.

undefined

 

【Answer】

You can piece together the above result set with the stored procedure. But the coding is complicated. You can use esProc to prepare the report data source: convert a 10-column, N-row result set to a 5-column, N*2-row one and add column headers to each page. The esProc SPL (Structured Process Language) script is as follows:

 

A

1

$select * from tb1

2

=create(c1,c2,c3,c4,c5)

3

=A1.group((#-1)\6)

4

=A3.run(A2.record(["column1","column2","column3","column4","column5"]|   ~.conj([column1,column2,column3,column4,column5])|["column6","column7",   "column8","column9","column10"]|~.conj([column6,column7,column8,   column9,column10])))

A1: Execute a SQL-like query to retrieve the 10-column, N-row result set.

undefined

A2: Create an empty two-dimensional table.

undefined

A3: Group A1’s table every 6 rows. The sign # represents row number and "\" enables a full division.

undefined

A4: Loop each of A3’s group to insert 14 rows to A2’s empty table in such a way: headers and 6 rows of the first 5 columns and headers and 6 rows of the last 5 columns. The sign ~ represents the current group.

undefined

A reporting tool can accesses an SPL script through esProc JDBC and calls it in the same way as calling a database result set. More explanations can be found in How to Call an SPL Script in Java.


SQL(179)
 
 •  544 View  •