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:

The source data the reporting tool needs are as follows:

esProc script for doing this:

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:

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.

A3 gets the final result of this example:

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:

Click on Preview to see the result report:

The way the reporting tool calls the esProc script is the same as that it calls the stored procedure. Save the esProc script in this example as group_concat.dfx, and it can be called by group_concat $P{arg} in JasperReport’s SQL designer.