July 21, 2015

esProc Arranges Data for Reports with Unconventional Layouts

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 ofcreatinghorizontal multi-column layoutusing 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 columnsin a “the horizontalfirst” 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:

A1:Execute an SQL statement, in which both begin and end are report parameters.
A2Create 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 throughthe 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: 

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 layoutbased 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:
sOrder
26
33
84
133
32
39
43
71
99
20
83
97
107
135
142
1
esProc script: 

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: 

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 transpositionwith 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: 

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: 

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 missingvalue 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: 


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:

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 expected report layout: 


esProc script:
 

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.