Author Topic: Concatenate  (Read 2246 times)

Offline aditya

  • Full Member
  • ***
  • Join Date: Nov 2008
  • Posts: 5
  • Forum Citizenship: +0/-0
Concatenate
« on: 03 Sep 2016 03:26:20 am »
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
Thanks & Regards,
AV

Offline raj_aries81

  • Community Leader
  • *****
  • Join Date: Jan 2009
  • Posts: 161
  • Forum Citizenship: +0/-0
Re: Concatenate
« Reply #1 on: 04 Sep 2016 07:53:09 pm »
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

Offline raj_aries81

  • Community Leader
  • *****
  • Join Date: Jan 2009
  • Posts: 161
  • Forum Citizenship: +0/-0
Re: Concatenate
« Reply #2 on: 04 Sep 2016 09:25:40 pm »
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

Offline aditya

  • Full Member
  • ***
  • Join Date: Nov 2008
  • Posts: 5
  • Forum Citizenship: +0/-0
Re: Concatenate
« Reply #3 on: 05 Sep 2016 12:14:09 am »
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'.


Thanks & Regards,
AV

Offline wzubke

  • Associate
  • **
  • Join Date: Sep 2016
  • Posts: 2
  • Forum Citizenship: +0/-0
Re: Concatenate
« Reply #4 on: 05 Sep 2016 07:53:28 am »
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!

Offline aditya

  • Full Member
  • ***
  • Join Date: Nov 2008
  • Posts: 5
  • Forum Citizenship: +0/-0
Re: Concatenate
« Reply #5 on: 05 Sep 2016 10:52:25 am »
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....
« Last Edit: 10 Sep 2016 12:53:55 pm by aditya »
Thanks & Regards,
AV

Offline ARod

  • Full Member
  • ***
  • Join Date: Sep 2016
  • Posts: 8
  • Forum Citizenship: +0/-0
Re: Concatenate
« Reply #6 on: 19 Sep 2016 05:17:44 am »
Been a long time with Impromptu -

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

 


       
Twittear