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:

esProc_report_unconvent_layout_1

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:

esProc_report_unconvent_layout_2

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:

esProc_report_unconvent_layout_3

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:

esProc_report_unconvent_layout_4

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:

esProc_report_unconvent_layout_5

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_report_unconvent_layout_6

esProc script:

esProc_report_unconvent_layout_7

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:

esProc_report_unconvent_layout_8

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:

esProc_report_unconvent_layout_9

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.

esProc_report_unconvent_layout_10

A3: This is the final result by appending data to A2:

esProc_report_unconvent_layout_11

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:

esProc_report_unconvent_layout_12

A1: Execute SQL statement to get data of 2013, and group and summarize it by months. Here is the result:

esProc_report_unconvent_layout_13

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:

esProc_report_unconvent_layout_14

A3:Loop through A1, retrieving one record each time and append it to A2 starting from the second field. Here is the final data:

esProc_report_unconvent_layout_15

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:

esProc_report_unconvent_layout_16

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:

 

 

 

 

esProc_report_unconvent_layout_17

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:

esProc_report_unconvent_layout_18

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:

esProc_report_unconvent_layout_19

Rows duplication

This scenario is to get three duplicates of the original records and display them in a report.

esProc script:

esProc_report_unconvent_layout_20

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:

esProc_report_unconvent_layout_21

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.
  1. 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_report_unconvent_layout_22

esProc script:

esProc_report_unconvent_layout_24

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:

esProc_report_unconvent_layout_25

 

A5: Return A2 to the reporting tool using JDBC.

Advertisements

About datathinker

a technical consultant on Database performance optimization, Database storage expansion, Off-database computation. personal blog at: datakeywrod, website: raqsoft
This entry was posted in Reporting tool. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s