Concatenating values from multiple rows

building reports webintelligence
1

I’m having some difficulty in understanding how(or IF) I can concatenate values from multiple rows.
I’m using BOE XI R2.
I’ve created a Webi that lists States and Sales amounts.
So far, so good.

What I’d like to be able to do is to concatenate all of the State names that I’ve selected into one long comma separated string:
e.g. Alabama,Alaska, Arizona, Michigan, New Mexico . . . . etc.

I come from a Crystal Reports background where this is easy to do.
It must be possible in Webi too (right??)

FredWalther :canada: (BOB member since 2006-09-14)

2

Unfortunately, not.

Steve Krandel :us: (BOB member since 2002-06-25)

3

Maybe the function ReportFilter([State]) can help?

filip :belgium: (BOB member since 2005-12-29)

4

That’s a really interesting idea. Build a filter that captures all values and then use the function to display the value of the filter.

Very cool.

Steve Krandel :us: (BOB member since 2002-06-25)

5

:wink:

You don’t even need a filter, because then this function will always show all the values of the variable used in the report.

filip :belgium: (BOB member since 2005-12-29)

6

Any similar brilliant ideas for XI Release 1?

Farhan Jaffery :us: (BOB member since 2005-08-27)

7

I have similar question. I have two tables. One is shipment the other package details. A shipment can have multiple packages. would like to see in report as one row: shipment and the multiple package id’s concatenated vs a row for each package. Can i do that? if so how?

Molly (BOB member since 2004-03-16)

8

Hi Fred,

I’m not really sure why you want to concatenate values in this way but you may try this (or something like this);
=[State]+Previous([State])+Previous(Previous([State])) .

Joe Szabo :us: (BOB member since 2002-08-19)

9

When I try this Joe I get #MULTIVALUE

ColumbiaIT (BOB member since 2011-06-15)

10

On MS SQL Server 2005 onwards, we use following trick to concatenate multiple column values and return a single row:

create table #t(i int, val int)
insert into #t values(1,1)
insert into #t values(1,2)
insert into #t values(1,3)
insert into #t values(1,4)
insert into #t values(1,5)
insert into #t values(1,6)
insert into #t values(1,7)
insert into #t values(2,10)

select * from #t

select distinct i ,REPLACE((select cast(val as varchar)+',' from #t t1 where t1.i=t2.i
for XML path(''))+'$',',$','') as val
from #t t2

Farhan Jaffery :us: (BOB member since 2005-08-27)