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:

esProc_report_horizontal_columns_1

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:

esProc_report_horizontal_columns_2

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

esProc_report_horizontal_columns_3

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:

esProc_report_horizontal_columns_4

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:

esProc_report_horizontal_columns_5

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:

esProc_report_horizontal_columns_6

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:

esProc_report_horizontal_columns_7

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:

esProc_report_horizontal_columns_8

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

esProc_report_horizontal_columns_9

 

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.

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