Variable expression for concatinating the field. Function like group_concat in My SQL

[srkr]

Hi ,

 

I am new to jasper. I am having a problem to design a report which does the same like group_concat() function in my sql..  i am using a sql server so 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 below expression.

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

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

 

this one is not working.. Could you please help me on this please

[calculate.machine]

     

[calculate.machine]

Both SQL and Jasper should be able to realize the goal of group_concat function. But the code will be difficult to write. You’d better try using esProc to assist Jasper. The following code will do completely the same thing as group_concat function does, without the database involved.

=myssqlDB.query("select * from table1").group(Col1,Col2,Col3;~.(Col4).string@d():Col4)

 

Similar to the database, esProc offers the JDBC interface to be integrated in Jasper. Please refer to http://blog.raqsoft.com/?p=2436 for details. 

[srkr]

Hi.. Thanks for your answer.

I have no idea about the esProc but I found the solution in different way.

[srkr]

Lets say Col4 is named as $F{Acc}t and we create a variable for Acct as $V{Acc_List}.
Use the below expression in the Variable and Make Initial Value to ""

($F{Acct} == null || $F{Acct}.equalsIgnoreCase("")) ? $V{Acc_List} :
$V{Acc_List}.equalsIgnoreCase("") ? $V{Acc_List}+$F{Acct}+"," :
($V{Acc_List}.contains($F{Acct}+","))? $V{Acc_List} : ($V{Acc_List}+$F{Acct}+",")

Once done output should be like

A12G3 , K78DE , MAT12,

with a comma extra end .. which can be removed by using this in text field expression.

$V{Acc_List}.substring( 0, $V{Acc_List}.length() -1)