It is difficult to produce unusual report layouts automatically with functionalities provided by reporting tools. But if we can prepare data sources in an appropriate way, the difficulty of building such a report will be significantly reduced.
To do that, we can use esProc (its free edition is available). It not only supports set operations, order-related calculations, dynamic script execution, but provides a simple and easy-to-use JDBC interface, through which the reporting tool will get the result of executing the esProc script, which can be identified as a database stored procedure and accept parameters from the reporting tool. See How to Use esProc to Assist Reporting Tools for details.
The following examples will show some familiar unusual layouts and offer the esProc solutions.
Horizontal multi-column layout
Most of the reporting tools support vertical multi-column layout, but few can handle horizontal multi-column layout. Yet this shortcoming can be remedied by using esProc to prepare the data set in advance:
Below is a selection of the database table emp that has three fields:
The desired layout is that data is sequentially presented in a horizontal way in 3 columns, as shown below:
By transforming the original 3-column table to a 9-colulmn table with esProc, you can create a horizontal multi-column layout with the reporting tool:
|
|
A |
B |
C |
|
1 |
=myDB1.query(“select EId,Name,Dept from emp where EId>=? and EId<=? order by EId “,begin,end) | ||
|
2 |
=A1.step(3,1) | =A1.step(3,2)|[null] | =A1.step(3,3)|[null] |
|
3 |
=A2.derive(B2(#).EId:EId2,B2(#).Name:Name2,B2(#).Dept:Dept2,C2(#).EId:EId3,C2(#).Name:Name3,C2(#).Dept:Dept3) | ||
Divide rows into three parts by their sequence numbers and store them respectively in A2, B2 and C2. And then concatenate the fields in B2 and C2 with those in A2:
Interlocking multi-column layout
Arrange the records of the database table emp horizontally in two columns. The record in the second column of the report’s each row will always be the first record in the next row’s first column, as shown below:
esProc script:
|
|
A |
B |
|
|
1 |
=myDB1.query(“select EId,Name,Dept from emp where EId>=? and EId<=? order by EId “,begin,end) | ||
|
2 |
=A1.conj([~[-1],~]).to(3,) | ||
|
3 |
=A2.step(2,1) | =A2.step(2,2)|[null] | |
|
4 |
=A3.derive(B3(#).EId:EId2,B3(#).Name:Name2,B3(#).Dept:Dept2) | ||
A2: An interlocking concatenation, which, in sequence, strings the current record and the previous one together. Get the concatenation result beginning from the third row. The result is like this:
Present the records horizontally in two columns, as the preceding case shows.
Dynamic vertical multi-column layout based on single-field data sets
Present a single-field data set in a report sequentially in a “vertical first” order. Both the row number and the column number are referenced by parameters. Below is the source data:
esProc script:
|
|
A |
|
1 |
=myDB1.query(“select orderID from sorder”).(orderID) |
|
2 |
=Row.new(${Col.(“:c”+string(~)).string()}) |
|
3 |
=Col.(A2.field(~,to((~ -1)*Row+1,~*Row).(A1.m(~)))) |
|
4 |
return A2 |
A1: Retrieve data from the single-field table and convert it to a sequence (an ordered set).
A2: Create a two-dimensional table with the same structure as the expected report, with the initial values being nulls. Row and Col are report parameters.
A3: This is the final result by appending data to A2:
Printing a wide table horizontally
The database table sOrderEmp is too wide to fit across one piece of paper. The report requires that each page be printed with column headings and column numbers and that the columns from the first to the Nth be printed on one page and those from the (N+1)th to the (2N)th be printed on the next page, and so forth. This is shown as follows:
esProc script:
|
|
A |
|
1 |
=myDB1.query(“select * from “+ argSource) |
|
2 |
=create(${argPageCol.(concat(“c”,~)).string()}) |
|
3 |
=A1.group((#-1)\argPageRow) |
|
4 |
=(fn=A1.fno()).step(argPageCol,1).(to(~,if((t=~+argPageCol-1)>fn,fn,t)).(A1.fname(~))) |
|
5 |
=A4.(“[” + ~.string@q() + “]|~.conj([” + ~.string()+” ])”).string(“|”) |
|
6 |
=A3.run(A2.record(${A5})) |
|
7 |
return A2 |
This standard code can be used to print any database argSource in argPagCol columns and argPageRow rows on each page. A2: Dynamically generate an empty two-dimensional table with argPageCol columns. A3: Group A1 every argPageRow rows. A4: Group A1’s field names every argPageCol ones. A5: Concatenate strings together to be processed dynamically by A6. A6: Loop through each group of rows in A3 and insert argPageCol*argPageRow records to A2 each time, with field names and values for each page being appended sequentially. A7: Return A2 to the reporting tool.
A2’s result is as follows:
Rows duplication
Get three duplicates of the original records and present them in a report.
esProc script:
|
|
A |
|
1 |
=myDB1.query(“select * from sOrder”) |
|
2 |
=A1.conj([~]*3) |
A2: [] indicates a sequence (an order set). [~] represents a single-member sequence containing the current record from A1. [~]*3 makes 3 duplicates of the current record. The conj function performs the duplication of every record of A1 and then the concatenation. This is the result:
Condition-controlled formats for grouped reports
Build a grouped report whose data comes from the database table sOrder, in which Seller is the grouping field, and Client and Amount hold the detail data. Here are the requirements:
1. In each group of detail data, the rows from the second down to the end will display “+”, but the first row will not display it.
2. If there is more than one row in each group of detail data, display a subtotal of the amounts at the end. No subtotal for a single-row group.
Below is the desired report layout:
esProc code:
|
|
A |
B |
C |
|
1 |
=myDB1.query(“select Seller,Client,Amount from sOrder where Amount>?”,arg) | ||
|
2 |
=create(item,value) | ||
|
3 |
for A1.group(SellerId) | >A2.insert(0:A3,if(#>1,”+”)+Client,Amount) | |
|
4 |
if A3.len()>1 | >A2.insert(0,A3.SellerId+”subtotal:”,A3.sum(Amount)) | |
|
5 |
return A2 | ||
Explanation: Query the database, group data by SellerId and loop through every group to append data values in the current group to the empty A2. Put “+” before the Client value if the sequence number “#” is greater than 1; append a subtotal row to A2 if there is more than one row in the current group. Below is the final result of A2:
Return A2 to the reporting tool via JDBC.
Inserting a sub-table dynamically into the main table
The database table dColThread is the main table, with tID being the primary key; dColQuestion is the sub-table, whose foreign key is tID. They are shown as follows.
dColThread
dColQuestion
The report needs to query the main table by ApplicationName and display the data in a list table. Each record of the main table corresponds to multiple status values but less than 5 ones. They need to be inserted horizontally between Phone and Decline fields of the main table, with field names being QuestionNo1, QuestionNo2…QuestionNo5. If all values in a column are nulls, the column won’t be shown.
Below is the desired layout:
esProc script:
|
|
A |
B |
|
1 |
=myDB1.query(“select * from dColThread t,dColQuestion q where t.tID=q.tID and t.ApplicationName=?”,arg1 ) | |
|
2 |
=A1.group(tID) | |
|
3 |
=create(ApplicationName,User,Phone,QuestionNo1,QuestionNo2,QuestionNo3,QuestionNo4,QuestionNo5,Decline) | |
|
4 |
for A2 | =A4.(status)|[“”,””,””,””,””] |
|
5 |
=A3.record(A4.ApplicationName|A4.User|A4.Phone|B4.to(5)|A4.Decline) | |
|
6 |
return A3 | |
Explanation: Retrieve the associated data from the main table and the sub-table using a SQL statement and group it by tID and loop through each group. Each loop will insert a record to the empty table sequence A3, during which fields of the main table will be inserted directly and the field of the sub-table will need to be converted into rows before the insertion, with the five fields being all supplied. Here’s the result of A3 after the loops:
Joining list tables horizontally
table 1 is a Oracle database table and table2.xlsx is a file. Both have the same structure. Below are selections of them:
We need to group table1 and table2 respectively by name, get the number of records in each group and calculate the sum of active field, and then present the results side by side. Below is the desired layout:
esProc script:
|
|
A |
|
1 |
=myDB1.query(“select name,count(*) Count,sum(isActive) Amount from table1 group by name”) |
|
2 |
=file(“D:\\table2.xlsx”).importxls@t() |
|
3 |
=A2.groups(name;count(~):Count,sum(active):Amount) |
|
4 |
=join@f(A1,name;A3,name) |
|
5 |
=A4.new(ifn(_1,_2).name:name,_1.Count:Count1,_1.Amount:Amount1,_2.Count:Count2,_2.Amount:Amount2) |
Explanation: Retrieve data from the database and the Excel file, and do a full join between them to put the fields we want in one data set. A5 stores the joining result, as shown below:
Inter-column calculation for the cross table
The database table store holds the sales amount of multiple products in 2014 and 2015. You need to present the annual sales amount of every product in a cross table and calculate the annual growth rate of every product. Below is a selection of the source data:
Below is the desired layout:
esProc script:
|
|
A |
|
1 |
=mydb.query(“select * from store order by item,year”) |
|
2 |
=A1.group(item).run(A1.record([“Growth Rate”,item,~(2).quantity/~(1).quantity-1])) |
|
3 |
return A1 |
Explanation: Columns of the cross table are generated dynamically. The inter-column calculation needs a dynamic second reference. To do it, it’s difficult to write a script with the reporting tool. Yet we can first use esProc to perform the inter-column calculations and append the results to the source data, and then we just need to take care of the design of the simple cross table.
A1’s result is as follows:
