【Question】

I am new to Jasper. I am having trouble in designing a report which does the same like group_concat() function in MySQL. I am using a SQL server so I cannot use that function in my query.

My input is something like this:

Col1    Col2    Col3     Col4

10    1234     Sam    A12G3

10    1234    Sam    K78DE

10    1234    Sam    MAT12

20    1456    Tom    E12F4

20    1456   Tom    KAR3R

20    3217    Tom   G45G4

my output should be like:

Col1    Col2    Col3    Col4

10    1234    Sam    A12G3 , K78DE , MAT12

20    1456    Tom    E12F4 , KAR3R

20    3217    Tom    G45G4

I am trying to create a variable using the following expression:

Class : java.lang.String , CALCULATION: Nothing , Incrementype: Group , Incrementgroup: Col2

expression is $F{Col4} + "," + $F{Col4}

It doesn’t work. Could you please help me with this?

【Answer】

You can do what the group_concat function does in both SQL and JasperReport. But it’s difficult to write the code. Try using SPL (Structured Process Language) to help with the reporting tool in handling this. The following SPL script does the same as group_concate function does, without referring to the database.

esProc provides JDBC interface to function like a database and to integrate with the reporting tool. For more details, see How to Call an SPL Script in JasperReport.