esProc Assists Report Development – Realization of group_concat

Uncategorized 456 0

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

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.

FAVOR (0)
Leave a Reply
Cancel
Icon

Hi,You need to fill in the Username and Email!

  • Username (*)
  • Email (*)
  • Website