Concatenate

[aditya]

Hello Techie,

Need one help!

How to concatenate same column value in impromptu report.
Scenario : I have two columns
Column1 Column2
1              A 
1              B
2               C
2                D

Expected result
Column1   Column2
1               A,B
2               C,D

Any input much appreciated

[raj_aries81]

One way of achieving it is using a repeater

1)Create a list object and Drag column1 into the list
2)Drag Repeater object next to the column1, repeater would be your column2 now
3)Unlock the report and Drag column2 into the repeater object. You can either include comma delimiter in the dataitem calculation or use text object
4)Use some string functions to get rid of the extra comma for the last value

Hope this helps

Regards
raj

[raj_aries81]

Alternate approach would be using a List & Crosstab together with a Master-Detail relation ship on Column 1 and then setting BoxType=None for Crosstab Columns and Crosstab Corner.

Please be aware of the performance implications if your data volume is huge.

Regards
Raj

[aditya]

Thanks Raj for reply..

Here we are using Impromptu report which is a very oldest version of cognos. So, repeater and Master-detail functionality is not available there. Also, we are using ERP tool (AS400) as a datasource and created ODBC connection to connect with 'CATALOG'.

[wzubke]

Are you able to use custom SQL to complete the group concatenation?

MSSQL can do this using FOR XML combined with STUFF()

MySQL comes with a handy group_concat() function that does this.

Hope this helps!

[aditya]

I am using 'Impromptu report'(version 6.1.2) it is a very oldest version of cognos. We are using DB2/400 as database, through ODBC connection we have connected the database for creating 'Catalog' for reporting.
These dababase functions (i.e Group_Concat, Listagg) are not working in this version.
Also, in Impromptu report we can use only function which are available in report, we cannot add functions mannually to override SQL in report....

[ARod]

Been a long time with Impromptu -

Can you keep your Column2 in external file and use it as Picklist prompt option for Column1 ?