esProc Assists Report Development – Create a Tabular Report by Transposition

Unconventional statistical tasks are difficult to be handled solely by the reporting tool like Jasper and BIRT, or the SQL. For example when the original data are not arranged as required by the tabular report, they need to be transposed for display. However, with the assistance of esProc that has a powerful computing engine for processing structured data and is integration-friendly, we can deal with this case more easily. The following example will teach you how data are transposed.

The database table KPIDetail stores detail data of performance assessment. The KPI Subtotals for different time ranges can be computed using simple SQL statements (group by or union), as shown below:

esProc_report_tabluar_transposition_1

As the following figure shows, the tabular report will be created based on those source data:

esProc_report_tabluar_transposition_2

It can be seen that the source data cannot be used directly for tabular report display and thus need to be transposed.

Taking the computation of KPISubtoal as an example, the following esProc code provides a universal method of transposing source data arranged in any format:

esProc_report_tabluar_transposition_3

A1=myDB1.query(“select * from KPISubtoal”)

Query the database for the source data. The result is as follows:

esProc_report_tabluar_transposition_4

A2=A1.fname().to(2,)

This line of code produces a set of A1’s field names except the first one. fname function creates a set of field names, for instance A1.fname()=[“range”,”registrations”,”deposits”,”games”]. to function returns a consecutive subsequence between two given integers of the original sequence, for instance .to(2,4) will return a subsequence consisting of the 2nd, 3rd and 4th members; if the second parameter is omitted, the subsequence will consist of members starting from the first parameter to the end of the original sequence. A2’s result is as follows:

esProc_report_tabluar_transposition_5

A3=create(KPI).record(A2)

This line of code creates a two-dimensional table for storing the transposed data. It only has one field – KPI – for the time being whose values come from A2, as shown below:

esProc_report_tabluar_transposition_6

A4: for A1

This loop statement traverses A1’s records, transposes the row data to column data and stores the transposed data in A3. The loop body consists of cells from B4 to B6, the indentation part; in it A4 is used to reference the loop variable.

B4=columnName=A4.#1

This line of code gets the value of the first field of the current record and assigns it to the variable columnName. We can also use A4.range to get the same result, but the sequence number of the field – #1 – is used here for the sake of universality. During the first loop, the value of columnName is “today”.

B5= A4.array().to(2,)

This line of code gets field values of the current record beginning from its second field. A4.array() gets the field values of the loop variable (i.e. the current record) to form a set. For the first loop, B5’s result is as follows:

esProc_report_tabluar_transposition_7

B6=A3=eval(“A3.derive(B5(#):”+columnName+”)”)

This line of code adds a new column to A3’s two-dimensional table. The column name is the value of columnName and its values are B5’s result. “#” represents the sequence numbers of A3’s members. eval function parses a string into an expression. For the first loop, B6’s expression is A3=A3.derive(B5(#):today). The result is as follows:

esProc_report_tabluar_transposition_8

After A4’s loop is executed, A3 will get all the transposed data as shown below:

esProc_report_tabluar_transposition_9

result A3

This line of code returns A3’s two-dimensional table to the reporting tool. esProc provides JDBC interface for integrating with the reporting tool that will identify esProc as a database. See related documents for the integration solution.

Then create a simple tabular report with, for instance, JasperReport. The template is as follows:

esProc_report_tabluar_transposition_10

A preview of the final report is as follows:

esProc_report_tabluar_transposition_11

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 Application, Reporting tool and tagged , , , . 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