esProc Assists Report Development – Horizontal Layout with Multiple Columns

Typically the reporting tool like Jasper and BIRT merely supports vertical data layout with multiple columns. They don’t support horizontal data layout with multiple columns, as the following report shows:

With the assistance of esProc that has a powerful computing engine for processing structured data and is integration-friendly, we can conveniently handle this kind of report creation. The method is illustrated through the following example.

The database table emp stores employee information, in which EId contains the employee numbers. We want to display data horizontally in three columns. In each column the data are arranged in the order of EID, Name and Dept. The data to be displayed are specified by an interval of employee numbers. Some of the original data are as follows:

The following esProc code is used to rearrange the 3-field table into a 9-field table:

A1=myDB1.query("select EId,Name,Dept from emp where EId>=? and EId<=? order by EId ",begin,end)

This line of code queries the database for desired data using SQL statements. Both begin and end are parameters passed from the report, specifying an interval of employee numbers. If begin=4 and end=20, then A1 retrieves the following data: 

A2=A1.step(3,1)

From A1, this line of code retrieves the first record of every three ones to create a two-dimensional table. step function’s first parameter is the step, the second represents the starting point of each retrieval. There should have been the third parameter that represents the number of records retrieved from the starting point and that is omitted here. A2’s result is as follows:

B2 and C2 perform similar computations. Since it is possible that either B2 or C2 has one less record than A2, each will be appended a null record at the end. The operator “|” means concatenating two sets. If begin=4 and end=20, results of A2, B2 and C2 are respectively as follows:

A3=A2.derive(B2(#).EId:EId2,B2(#).Name:Name2,B2(#).Dept:Dept2,C2(#).EId:EId3,C2(#).Name:Name3,C2(#).Dept:Dept3)

This line of code joins B2 and C2 into A2. derive function appends one or more new fields. B2(#).EId:EId2 is the expression of the first new field, in which “#” represents the sequence number of every record of A2, “B2(#).EId” retrieves the EId field of the #th record from B2 and “:EId2” renames the field “EId2”. By appending 6 new fields to A2, A3 gets the final data the report needs:

result A3

This line of code returns A3’s 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 report with, for instance, JasperReport. The template is as follows:

Define two parameters – pbegin and pend – in the report to correspond to their counterparts in the esProc script.

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 horizontalColumn.dfx, to be called by call horizontalColumn($P{pbegin},$P{pend}) in JasperReport’s SQL designer.