The group_concat function is used in report development.

The group_concat function is used in report development.

When using report tools such as Jasper or BIRT, some unconventional statistics are often encountered, and the report tools or SQL statements are difficult to process, such as in MSSQL/Oracle, A report similar to the calculation result of group_concat function is displayed.

The centralized computing appliance has a structured and strong computing engine, which is easy to integrate and can help report tools to easily meet such requirements. The following uses MSSQL as an example to illustrate the implementation process of the general group_concat function.

Table 1 of MSSQL has four fields: Col1, Col2, and Col3 are grouping fields, and Col4 are summary fields. Some data is as follows:

The data sources required by the report tool are as follows:

Code of the Set calculator:

A1 = mssqlDB. query ("select * fromtable1 where Col1 in" + arg)

This code executes the SQL statement to retrieve data from the database. Here, arg is a parameter from the report, for example ). The calculation result of A1 is as follows:

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

This code groups A1 by Col1, Col2, and Col3, and concatenates Col4 in each group of data with commas. ~ In the code ~ Indicates each group of data. For example, the first group has three records .~. (Col4) indicates to retrieve the Col4 fields in each group of data. For example, the first group is the set [A12G3, K78DE, MAT12]. The string function Concatenates the members in the set into a string. The default Delimiter is Comma. Option @ d indicates that no quotation marks are given to the Members. Therefore, the expression [A12G3, K78DE, MAT12] is used. string @ d () is equal to "A12G3, K78DE, MAT12 ". The expression ": Col4" indicates renaming the previous calculation result to Col4.

A3 is the final calculation result of this case, as follows:

A3: result A2

This code returns the data in A2 to the report tool.

The assembler provides a JDBC interface. The report tool recognizes the assembler as a common database. For the integration solution, see related documents.

 

The following uses JasperReport as an example to design a report. The table is as follows:

After previewing, you can see the report results:

Note that the method of the report call set calculator is the same as that of the call stored procedure. For example, save this script as group_concat.dfx, you can use group_concat $ P {arg} in the SQL designer of JasperReport.