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:

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

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:

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

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

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, 3^rd 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:

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:

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:

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:

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

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:

A preview of the final report is as follows: 

The reporting tool calls the esProc script in the same way as that in which it calls the stored procedure. Save the esProc script as, say esProcKPI.dfx, to be called by esProcKPI in JasperReport’s SQL designer. If query parameters have been entered to the esProc script, then use esProcKPI $P{parameter} to call the script.