Dynamically Display Columns with Page Break
【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.
【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.
A2: Create an empty two-dimensional table.
A3: Group A1’s table every 6 rows. The sign # represents row number and "\" enables a full division.
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.
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.
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