It’s hard to create reports with unconventional layouts directly with functions provided by a reporting tool. However, if the data for building such a report can be beautifully prepared, the difficulty could be considerably reduced.
esProc(free edition is available) is an expert at handling this data preparation. It provides set operations, supports dynamic script execution, as well as offers a handy JDBC interface through which the reporting tool gets the result of executing the esProc script, which will be identified as a database stored procedure and accept parameters from the reporting tool.
Below is structure of integration of an esProc script and a reporting tool:
An example of creating horizontal multi-column layout using esProc:
Most reporting tools support vertical layout but they are really not good at arranging records horizontally in multiple columns. Below is a selection of the database table emp, which has three fields:
|
EId |
Name |
Dept |
|
4 |
Emily |
HR |
|
5 |
Ashley |
R&D |
|
6 |
Matthew |
Sales |
|
7 |
Alexis |
Sales |
|
8 |
Megan |
Marketing |
|
9 |
Victoria |
HR |
|
10 |
Ryan |
R&D |
|
11 |
Jacob |
Sales |
The expected layout puts records continuously in 3 columns in a “the horizontal first” order, as shown below:
By transforming the original 3-column table to a 9-colulmn table with esProc, you can create a horizontal layout directly with the reporting tool. Below is the esProc script:
|
|
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) | ||
|
4 |
result A3 | ||
A1: Execute an SQL statement, in which both begin and end are report parameters.
A2：Create a new two-dimensional table by retrieving the first record every three ones from A1. B2 and C2 do the same thing in the same manner. It’s possible that B2 and C2 have one less record than A2, so you need to append a null record to each of them. The operator “|” means concatenating two sets. The result is:
A3: Join B2 and C2 with A2. B2(#).EId is the first newly-added field, meaning getting EId field of the #th record from B2. The sign “#” stands for the sequence number of each record of A2. The result is the data the report creation needs, as shown below:
The reporting tool calls the esProc script via JDBC, in a same manner as it calls the stored procedure from a normal database. The syntax is this: call esProc script name (para1…paraN). The result returned from the script participates in report creation in the form of a normal data set. Details are covered in the following documents: esProc Integration & Application: Integration with JasperReport and esProc Integration & Application: Integration with BIRT.
As a professional tool for handling data sources for reporting tools, esProc has more capabilities, which will be shown through the following cases.
Interlocking multi-column layout
Arrange the records of the database table emp horizontally in two columns. The rule is that the record in each row’s second column will always be the first record in its next row’s first column. This is the expected report layout:
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. The concatenation result appears from the third row. Here is the result:
A3, B3, A4: Arrange the data horizontally in two columns, as the preceding case shows.
Dynamic vertical multi-column layout based on single-field data sets
You need to display a single-field data set in a report in a “the vertical first” order. Both the row number and the field number are referenced by parameters. Below is the source data:
| sOrder26
33 84 133 32 39 43 71 99 20 83 97 107 135 142 1 |
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 |
result 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 and the initial values being nulls. Row and Col are report parameters.
A3: This is the final result by appending data to A2:
Simple row-to-column transposition
The database table SALES holds order data. Below is a selection of the source data:
| OrderID | Client | SellerId | Amount | OrderDate |
|
1 |
DSG |
13 |
19480 |
2014-06-20 00:00 |
|
2 |
ERN |
18 |
979 |
2014-06-13 00:00 |
|
3 |
JFE |
19 |
28972 |
2014-12-11 00:00 |
|
4 |
OFS |
21 |
4829 |
2014-02-24 00:00 |
|
5 |
ERN |
22 |
21392 |
2014-02-01 00:00 |
You need to calculate the total order amount, the biggest order amount, the smallest order amount and the total number of orders in every month of the year 2013, and then transpose the resulting data into a table with 13 fields and 4 rows. The four operations form the first field, subtotal. Each month forms a field, with field names being 1, 2, 3, 4… The first five fields are as follows:
| subtotal |
1 |
2 |
3 |
4 |
… |
| OSum |
4176606 |
3645032 |
4361721 |
4670117 |
… |
| OMAX |
27997 |
28021 |
27936 |
28022 |
… |
| OMIN |
116 |
232 |
272 |
104 |
… |
| OCount |
295 |
265 |
322 |
324 |
… |
esProc script:
|
|
A |
|
1 |
=db.query(“select month(ORDERDATE) as MONTH,sum(AMOUNT) as OSum,max(AMOUNT) as OMAX, min(AMOUNT) as OMIN ,count(ORDERID) as OCount from sales where year(ORDERDATE)=2013 group by MONTH order by MONTH”) |
|
2 |
=[“OSum”,”OMAX”,”OMIN”,”OCount”].new(~:subtotal,${to(A1.len()).string()}) |
|
3 |
=A1.run(A2.field(#+1,OSum|OMAX|OMIN|OCount)) |
|
4 |
result A2 |
A1: Execute SQL statement to get data of 2013, and group and summarize it by months. Here is the result:
A2: Create an empty table sequence with default values for storing the final data. There are 13 fields altogether and here are the first several fields:
A3: Loop through A1, retrieving one record each time and append it to A2 starting from the second field. Here is the final data:
A4: Return A2 to the reporting tool.
Row-to-column transposition with dynamic member location
In the database table tb, every three records with the same userid form a group. You need to develop a report where the data groups are rearranged into rows of data. Below is a selection of tb:
|
userid |
type |
descr |
|
scooby |
dog |
dog |
|
scooby |
weight |
50 |
|
scooby |
hair |
long |
|
mickey |
mouse |
mouse |
|
mickey |
hair |
|
|
mickey |
weight |
2 |
The expected report layout:
|
userid |
type |
hair |
weight |
|
mickey |
mouse |
2 |
|
|
scooby |
dog |
long |
50 |
esProc script:
|
|
A |
|
1 |
=$select * from tb |
|
2 |
=A1.group(userid).new( userid,(t=~.align@n([‘hair’,’weight’],type).(descr))(3):type,t(1):hair,t(2):weight) |
A1: Database query.
A2: Group data and convert each group to a record. group function performs data grouping. new function creates a two-dimensional table. “~” represents a group of grouped data. align function matches records to members of a set (here is [‘hair’,’weight’]); the @n option adds an extra row to hold records that fail to be matched. Here is the result:
Multi-layer row-to-column transposition
In the database kpi, every 4 records with the same f_site constitute a group. Below is a selection of kpi:
|
dataset_date |
f_site |
ioh_kpi |
idh_kpi |
iol_kpi |
|
2015/04/21 13:15 |
X6SF_SARF1 |
1 |
2 |
3 |
|
2015/04/21 13:30 |
X6SF_SARF1 |
9 |
1 |
2 |
|
2015/04/21 13:45 |
X6SF_SARF1 |
8 |
9 |
1 |
|
2015/04/21 14:00 |
X6SF_SARF1 |
7 |
8 |
9 |
|
2015/04/21 13:15 |
XC_01 |
2 |
3 |
4 |
|
2015/04/21 13:30 |
XC_01 |
11 |
12 |
13 |
|
2015/04/21 13:45 |
XC_01 |
21 |
22 |
23 |
|
2015/04/21 14:00 |
XC_01 |
31 |
32 |
33 |
Group data by f_site and perform row-to-column transposition based on each group so that you can get a report like this:
|
site |
KPI Name |
2015/04/21 13:15 |
2015/04/21 13:30 |
2015/04/21 13:45 |
2015/04/21 14:00 |
|
X6SF_SARF1 |
ioh_kpi |
1 |
9 |
8 |
7 |
|
idh_kpi |
2 |
1 |
9 |
8 |
|
|
iol_kpi |
3 |
2 |
1 |
9 |
|
|
XC_01 |
ioh_kpi |
2 |
11 |
21 |
31 |
|
idh_kpi |
3 |
12 |
22 |
32 |
|
|
iol_kpi |
4 |
13 |
23 |
33 |
esProc script:
|
|
A |
B |
|
1 |
=myDB1.query(“select * from kpi order by f_site,dataset_date”) | |
|
2 |
=A1.id(dataset_date) | =A1.fname().to(3,) |
|
3 |
=create(site,’KPI Name’,${A2.string()}) | |
|
4 |
for A1.group(f_site) | = A4.align(A2,dataset_date) |
|
5 |
>B2.run(A3.record([A4.f_site,~]|B4.field(~))) | |
|
6 |
result A3 | |
A2: Get dataset_date values that are distinct from each other – which is [“2015-04-21 13:15″,”2015-04-21 13:30″,”2015-04-21 13:45″,”2015-04-21 14:00”].
B2: Get A1’s field names from the third one – which is [“ioh_kpi”,”idh_kpi”,”iol_kpi”]
A3: Create an empty table sequence for storing the final result set, whose field names are site,KPI Name,”2015-04-21 13:15″,”2015-04-21 13:30″,”2015-04-21 13:45″,”2015-04-21 14:00″.
A4: Group A1 by f_site and run a loop through every group. B4 aligns A4 to A2, during which a missing value will be filled with a default one. B5 appends records to A3 dynamically.
A3 has the final result which will be returned to the reporting tool by A6. The final result is as follows:
Rows duplication
This scenario is to get three duplicates of the original records and display 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. conj function performs the duplication of every record of A1 and then the concatenation. This is the result:
Condition-controlled formats for grouped reports
In this case, a grouped report is required. The 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:
- 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.
- 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 expected report layout:
esProc script:
|
|
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 |
result A2 | ||
A1: A SQL query.
A2: Create an empty two-dimensional table to store the final result set.
A3: Group A1 by Seller and loop through every group. The working range of the loop statement is the indented cells B3-C4, in which A3 is used to reference a loop variable.
B3: Append data values in the current group to A2. Put “+” before the Client value if the sequence number “#” is greater than 1.
B4-C5: Append a subtotal row to A2 if the number of rows of the current group is greater than 1. Below is the result that A2 will get eventually:
A5: Return A2 to the reporting tool using JDBC.