esProc Assists Report Development – Realization of group_concat

Quite a few unconventional statistical computations are difficult to handle with the reporting tools, like Jasper or BIRT, alone or in SQL. For example, to achieve an effect in a report similar to that of the group_concat function based on MS SQL/Oracle.

Having a powerful, integration-friendly computational engine for structured data computing, esProc can assist the reporting tool to make the realization of the same effect more conveniently. The following example will show you how to realize group_concat function in esProc.

Table1 in MSSQL has four fields, in which Col1, Col2 and Col3 are group fields and Col4 is summary field. Some of the data are as follows:

esProc_reporting_group_concat_1

The source data the reporting tool needs are as follows:

esProc_reporting_group_concat_2

esProc script for doing this:

esProc_reporting_group_concat_3

A1=mssqlDB.query(“select * from table1 where Col1 in”+arg)

This line of code retrieves data from MSSQL by executing the SQL statement. arg is a parameter passed from the reporting tool, such as (10,20). The result is as follows:

esProc_reporting_group_concat_4

A2=A1.group(Col1,Col2,Col3;~.(Col4).string@d():Col4)

This line of code groups data by Col1, Col2 and Col3 and concatenates the strings in Col4 together by commas.  ~ represents each group of data. The first group, for instance, has three records. ~.(Col4) means retrieving Col4 field from each group. It is the set [A12G3 , K78DE , MAT12] for the first group. string function concatenates members of a set into a string, delimited by commas by default. @d option forbids surrounding members of the set with quotation marks. Thus expression [A12G3 , K78DE , MAT12].string@d() is equivalent to “A12G3 , K78DE , MAT12”. Expression “:Col4” means renaming the result of computing the previous expression as Col4.

esProc_reporting_group_concat_5

A3 gets the final result of this example:

esProc_reporting_group_concat_6

A3: result A2

This line of code returns the result of A2 to the reporting tool.

esProc provides the JDBC interface to be integrated with the reporting tool, which will recognize esProc as a database. Please refer to related documents for the integration solution.

Now let’s design the report based on, for instance, JasperReport. The appearance and layout is as follows:

esProc_reporting_group_concat_7

Click on Preview to see the result report:

esProc_reporting_group_concat_8

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