Syntax for concat() and Join() function

Hi Team,

Please tell me how to use the functions concat() and join() which are presented in the AGGREGATE menu item.

I am in need of appending four different values which are displayed in a particular column.

For ex:

GRADE
A
B
C
D

Expected output should be

GRADE
A,B,C,D

Please tell me whether the concat() and join () functions solve my purpose in Actuate version 9...

And also kindly tell me how to use these functions along with syntax.

Actuate Version10:
In the latest version we have also have a function CONCATENATE inside the AGGREGATE list.

If you have any idea on this function, please advice me in this as well.

Thanks

Find more posts tagged with

Comments

pricher

The following forum post may help you understand the CONCATENATE function. 
 
<a class='bbc_url' href='http://www.birt-exchange.org/org/forum/index.php/topic/21170-aggregating-data-by-row/'>Forum Post</a> 
 
P.

235383

<blockquote class='ipsBlockquote' data-author="'pricher'" data-cid="75411" data-time="1301923031" data-date="04 April 2011 - 06:17 AM">
The following forum post may help you understand the CONCATENATE function. 
 
<a class='bbc_url' href='http://www.birt-exchange.org/org/forum/index.php/topic/21170-aggregating-data-by-row/'>Forum Post</a> 
 
P. </blockquote>
 
Hi Pricher, 
 
Thanks for ur post. I did see that. 
 
My doubt is that do we have CONCATENATE function in the aggregate tool. 
 
I did see CONCATENATE only in version10 and not in version9. 
 
Correct me if i am wrong.. 
 
 
Thanks 
prasad

pricher

If you don't see CONCATENATE in V9, it probably means it's been added in V10. (Sorry, I don't have access to an earlier version to confirm that). 
 
In the meantime, if you need to concatenate the values of multiple rows into one field, your best bet is to use scripting to create your own variable. To do this: 
 
<ul class='bbcol decimal'><li>Declare a global variable in the initialize() method of the report;</li><li>Concatenate the values of each row into that global variable by writing code in the onCreate() method of the detail row of your table;</li><li>Display the value of that variable in a data item or text field.</li></ul>
Let me know if you need an example. 
 
P.

Sankari

<blockquote class='ipsBlockquote' data-author="'pricher'" data-cid="75438" data-time="1301945325" data-date="04 April 2011 - 12:28 PM">
If you don't see CONCATENATE in V9, it probably means it's been added in V10. (Sorry, I don't have access to an earlier version to confirm that). 
 
In the meantime, if you need to concatenate the values of multiple rows into one field, your best bet is to use scripting to create your own variable. To do this: 
 
<ul class='bbcol decimal'><li>Declare a global variable in the initialize() method of the report;</li><li>Concatenate the values of each row into that global variable by writing code in the onCreate() method of the detail row of your table;</li><li>Display the value of that variable in a data item or text field.</li></ul>
Let me know if you need an example. 
 
P. </blockquote>
 
 
Hi pricher, 
 
Thanks for ur reply 
 
Yes i would be grateful if u explain me with an example.

pricher

Hi,

Attached is an example built with BIRT V9. Look at the script in report initialize() and table's detail onCreate().

Hope this helps,

P.

Sankari

<blockquote class='ipsBlockquote' data-author="'pricher'" data-cid="75490" data-time="1302026463" data-date="05 April 2011 - 11:01 AM">
Hi, 
 
Attached is an example built with BIRT V9. Look at the script in report initialize() and table's detail onCreate(). 
 
Hope this helps, 
 
P. </blockquote>
 
Hi pricher, 
 
Thanks for ur help. 
 
The above script works fine where there is no groupby condition in the reports. 
 
My report has various groupby conditions. 
 
If i include your script in the detail row , it appends all the values irrespective of the groupby condition. 
 
I gave the script in the detail row. Am i right. 
 
Please advice me how can v achieve this appending concept when there are groupby conditions in the report. 
 
And also advice me whether i need to add the script in the innermost group header or on the detail row's oncreate().

pricher

If you want to concatenate by group, simply re-initialize the variable to empty on each new group. You can do that by adding

cityList = ::

to the onCreate() method of the group header section.

See updated design attached.

P.

235383

Hi
Thanks for ur help.
It is working perfectly.
Sorry for the late reply....

235383

Hi,
As of now all my doubts are clear and working fine.

I have a new query which is a continuity of this appending issue.

My report looks like this:(After all the grouping)

NAME,NUM,AGE,CITY,PINCODE
AAA,123,34,NJ,001
NAME,NUM,AGE,CITY,PINCODE
BBB,124,23,NJ,002
NAME,NUM,AGE,CITY
CCC,125,45,NY
NAME,NUM,AGE,CITY
DDD,126,34,NY
NAME,NUM,AGE,CITY,****
EEE,127,34,NY.MALE

All the values like NAME,NUM,AGE etc are appended values of various rows of particular column. They are not column names and FIELD names.
Similarly AAA,123 etc are appended values from various rows of single column.

My requirement:
The repeated values of records NAME,NUM..etc has to be displayed oly once for the each group as below.
The above report has to be displayed like below.

NAME,NUM,AGE,CITY,PINCODE
AAA,123,34,NJ,001
BBB,124,23,NJ,002

NAME,NUM,AGE,CITY
CCC,125,45,NY
DDD,126,34,NY

NAME,NUM,AGE,CITY,****
EEE,127,34,NY.MALE

PLease note:
Since the values NAME ,NUM etc are appended from various rows, i am unable to group ,for getting the above result.

Please advice me on this issue . I am badly in need a help.

Thanks

235383

Hi pricher,

Thanks for uploading the report Concatenatebygroup. It solved my purpose.

I have one small query in this

I did see the following code in your report.

valtxt += (valtxt != "" ? "/ " : "") + this.getRowData().getColumnValue("AA");

Please tell me how can we provide necessary spaces between the appended values in the above code.

It now displays like
a,b,c,d,e

It want it to be diplayed as
a , b , c , d, e

Kindly reply me ASAP