cancel
Showing results for 
Search instead for 
Did you mean: 

How do I concatenate a single field from multiple rows in BOXI 3.1 WebI?

Former Member
0 Kudos

Hi, 

I'm using BOXI 3.1 WebI and have a need to show multiple values of a column in a comma delimited list.  I tried using a horizontal table but if the list goes over the right margin it creates a new page instead of wrapping to the next line like we want.  I can do what I want in Oracle sql using the WM_CONCAT aggregate function but haven't found a way to do it in WebI reports.  Has anyone figured out a way to do this?

Thanks,

John Klein

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Jothi,

jyothirmayee_s
Active Contributor
0 Kudos

HI John,

Did you tried to attach anything?. I dont see any though?.

Please edit and repost.

Thanks,

Jothi

jyothirmayee_s
Active Contributor
0 Kudos

Ok john,

Got it.

Below is the example that helps you to concatenate the row values into single column.

Let us take an example from eFashion universe.  I am picking [LINES] and [CATEGORY] objects from the Product class for my example:

In the result set, I am restricting the rows (for simplicity) for LINES = Dresses, Jackets and Leather.

For the above data, what needs to be achieved would look something like below:

A method on how this can be achieved at report level in WebI is explained below:

·        Create a variable [VAR Max Category] = Max([Category]) In ([Lines]) to find the maximum category, with respect to each Line

·        Create a variable [VAR Concat Category] = [Category] +", "+ Previous(Self), to concatenate the category value with it's previous row value

The records would look like this:

·        Create a 3rd variable [VAR Max Concat Category] = [VAR Concat Category] Where ([Category]=[VAR Max Category]).  This variable basically gives only those rows of [VAR Concat Category], where the value of the category is maximum for that particular Line

On replacing VAR Concat Category with VAR Max Concat Category column in the above screenshot, we get:

I have displayed Category and Var Max Category only for illustration purposes.  It is actually not required to be present / displayed in the report.  On removing those two columns:

Now, the only pending thing to do is to display those categories that belong to their Lines, against each of their respective Lines.  The below formula would do the trick:

·        Create a variable [VAR Category] =If(IsNull(Previous([VAR Max Concat Category]));Substr([VAR Max Concat Category];1;Length([VAR Max Concat Category])-2);Substr([VAR Max Concat Category];1;Pos([VAR Max Concat Category];Previous([VAR Max Concat Category]))-3))

Basically, the formula checks if the previous value of VAR Max Concat Category is NULL.

If TRUE, then it just displays VAR Max Concat Category

In the above screenshot, this scenario occurs for LINE = Dresses.  There is no value for VAR Max Concat Category prior to LINE = Dresses and hence it is NULL.

If FALSE, then previous row value of VAR Max Concat Category is removed from the present row value of VAR Max Concat Category

This scenario occurs for LINE = Jackets and LINE = Leather (and all the rows that may occur below).  This is done with the help of POS (for finding the position where the previous value of VAR Max Concat Category starts), and SUBSTR functions

This is the final output what we intended to achieve at the beginning of this post:

Somehow not able to insert any images. so only go with text pls.

Try this.. It should work.

Thanks,

Jothi

Former Member
0 Kudos

Thanks Jothi - It Works!

The only thing I changed was I switched the order of the concatenation so it would display in alphabetical order.  [VAR Concat Category] = Previous(Self) + ", " + [Category].

Thanks again!

John


jyothirmayee_s
Active Contributor
0 Kudos

Glad it worked out.

Thanks,

Jothi

Former Member
0 Kudos

This solution doesn't seem to work in 4.0. 

It seems to fail at 'var Concat Category'

If you don't specify your 'Line' <> the previous 'Line', it will include Lines fro the Previous Category,

 

Once that's handled, nothing is returned if there is only 1 value.  That can be handled also, but the last bit, I haven't found a way around. Each occurrence of 'var Concat Category' only returns the last previous and the current... so if I have 4 values only 2 are ever displayed, 

Has anyone managed to replicate this solution id 4.x?

Jody

0 Kudos

Hello Jyothirmayee,

Great work. Appreciated.

I am trying to do it in reverse.

The output looks like below

Column | aaaaa;bbbbb;ccccc;ddd;eeeeeee;ffffff

I want to display the same column like below

Collumn

------------

aaaaaa

-----------

bbbbb

----------

ccccc

---------

ddd

----------

eeeeeee

------------

ffffffffffffff

Can you please provide me the solution if any

Thanks in Advance,

Sukumar

Former Member
0 Kudos

Hi Experts,

i am using BI 4.1 SP 05

The database is sql server 2008

i am trying to achieve the same functionality,

i followed all the steps you mentioned above but i end up getting only first row where the desired concatenated filed is concatenated but for the rest of the rows is get #UNAVILABLE error.


See the screenshot for more information.

Please let me know if you need anymore information.

Thanks in advance for the help.

Athar

former_member462823
Participant
0 Kudos

HI Krupa,

DId you get solution to your query.

even I am facing similar issue.

Can one one let me know the solution for this.

Converting single row to multiple rows

Ex: Not assigned; domestic; commercial

Output

Not assigned

Domestic

Commercial

after etting the data in multiple rows I should assign keys to them-

If(value= Not assigned) then "123"

Else if (value = Domestic) then "234"  etc etc

Thanks

Answers (10)

Answers (10)

omacoder
Active Contributor
0 Kudos

For those following this thread, please support:

https://influence.sap.com/sap/ino/#/idea/140067

Former Member
0 Kudos

use ReportFilter([State])

Former Member
0 Kudos

This is great for a standard table, but it doesn't work with a cross tab. I need a true aggregate. I've tried using WM_CONCAT() around an object, but Webi thinks it's a regular Dimension and not an Aggregate (even though I've selected it as such). The problem is that it throws it into the GROUP BY and that throws an error. I've tried surrounding that statement with an aggregate function Webi recognizes like MIN/AVG, but then it splits it out into two queries for some reason instead of a single one.

I just need a way to overwrite it and say "no matter what you think, do NOT include this in the GROUP BY". Is that possible?

Former Member
0 Kudos

i did something in my previous project , i dont really remember , but i do have the code with me , its like 4 or 5 variables u have to make . I am just copying and pasting over here. May be helpful to you , so they are two way

WAY 1 :

Var_Ctegory C9

=If(IsNull(Previous([Var_ Max concatnate  C9]));Substr([Var_ Max concatnate  C9];1;Length([Var_ Max concatnate  C9])-2);Substr([Var_ Max concatnate  C9];1;Pos([Var_ Max concatnate  C9];Previous([Var_ Max concatnate  C9]))-3))

Var_ Max concatnate  C9

=[Var concate Project C9] Where ([Project Concat-9] =[Var_Max_Project C9])

Var_Max_Project C9

=Max([Project Concat-9]) In([Country Name])

Var concate Project C9

=[Project Concat-9]+", "+ Previous(Self)

Project Concat-9

=([Country (Final Global ICF Available)].[Project Number] )Where (Not(IsNull([Country (Final Global ICF Available)].[Final Global Icf Avail Ac])) And Not(IsNull([Var_Country (Final Global ICF Available).First Site Initiated Ac])))

OR

WAY 2:

Var_Ctegory 8 NEW

=If(IsNull(Previous([Var_Ctegory 8]));Substr([Var_Ctegory 8];1;Length([Var_Ctegory 8])-2);Substr([Var_Ctegory 8];1;Pos([Var_Ctegory 8];Previous([Var_Ctegory 8]))-3))

Var_Ctegory 8

=Substr([Var_ Max concatnate  8];3;Length([Var_ Max concatnate  8])-2)

Var_ Max concatnate  8

=[Var concate Project 8] Where ([Project Concat-F] =[Var_Max_Project 8])

Var_Max_Project 8

=Max(([Project Concat-8]))  In([Country Name])

Var concate Project 8

=[Project Concat-8] +", "+ Previous(Self)

Project Concat-8

=If Not(IsNull([Var_Final Protocol Available]))  And Not(IsNull([Country (Final Protocol Available) (1)].[1st Site Selected - Actual]) )Then[Project Number]

Former Member
0 Kudos


In below scenario i have 20141109,20141574 Po Num For same exped date (05-08-14)

But i want to display it as below

the above function doesn't work in this scenario......Pls Help

Former Member
0 Kudos

Hi John,

This article is really very useful. But I am working on BO 4.1 and the variable  [VAR Concat Category] = [Category] +", "+ Previous(Self) is not giving the result the way it should have.


I have also attached the screen shot. Please help me out , as i have to implement this logic in some other report.



Former Member
0 Kudos

Hi Vinod,

Since Category field is part of the table/report you wouldn't see the desired output; Please remove the [Category] field from your table and you will get the output you excpected.

If you want to keep the [Category] then you can read my swuggestion here;

In my previous comment i made a little modification to the solution suggested in this article in odered to get an easier solution.

I put everything here to avoid confussion with other good solutions given above;

[VAR Max Category] = Max([Category]) In ([Lines])

[VAR Concat Category] = If IsNull(Previous(Self;([Lines])))Then [Category] Else [Category]+", "+Previous(Self;([Lines]))

This will prevent concatenating Category fields from other [Lines] and ignor null values hence you don’t need the trick of [VAR Category] as suggested in this article; you need to modify [VAR MaxConcat Category] as follows;

[VAR MaxConcat Category]= [VAR Concat Category] where ([Category]= [VAR Max Category])

The Qualification of this variable should be ‘detail’ of the [Lines]; in the variable editor window set the qualification of this variable to 'detail' and the associated dimension to [Lines].

The above three steps are pretty much working from me in all situations; another advantage of making the last variable a 'detail' of [Lines] is you can use the variable in any report where [lines] is used. This just a little work around on the solution provided by Jothi and all creadit should be given to the orginal solution.

Thanks,

Hailu

Former Member
0 Kudos

Thanks a lot Hailu. It worked. You have explained it really very nice and in a precise way.

Thanks again.

Vinod Ranjan

Former Member
0 Kudos

for me its not working , i have attached the screen shot , please advise

Project

Var_Max_Project 8

Var Project Concat

Var_ Max concatnate  8

105569

206415

105569,

206361

206415

206361,

206415

206415

206415,

206415

,

, , , 206415, 206361, 105569,

following variable i have used
=[Project Number]+", "+Previous(Self)
Former Member
0 Kudos

Hello Hailu,

Thanks for this post. It really helped me, see the example below:

However, I do got one last issue with this.

I want to count the number of customers that took a certain path (see the table example from below):

I don't know how to reach this solution in BO4, because the computation in BO4 is based on the previous row values and a concatenation. Next to that, I don't think BO4 understands that certain Routes are the same and it therefore cannot count the number of routes.

Does anyone knows a solution to this?

Former Member
0 Kudos

Hello! Tim

If I understand you correctly, you need to make a path base 'Brand' and 'Date' which are the two fields you want to control your Routes.

I can’t see any easier solution than creating a new variable by combining these two fields; then you can apply the Technique in this discussion to concatenate the new variable but don’t forget to remove the date part from the new variable… again since the new variable will have a text date type, if your Date data involves different years then you may need to format the date to achieve proper sorting based on Date (like "yyyy/MM/dd")..

I hope the steps that follow will give you the output you are looking for..

Create a New Variable

Route=FormatDate([Date];"yyyy/MM/dd")+[Brand]

While concatenating the rows you can remove the date (first 10 chars from Route to 

Max_Route = Max([Route]) In ([Customer Name])

Concat_Route = If IsNull(Previous(Self;([Customer Name])))Then Right([Route];Length([Route])-10) Else Right([Route];Length([Route])-10)+", "+Previous(Self;([Customer Name]))

then your Routes will be

Routes=[Concat_Route] where ([Route]= [Max_Route]) which is detail of the [Customer Name].

Former Member
0 Kudos

Thanks Hailu,

I will try this later!

Former Member
0 Kudos

Hi

It is fantastic article and a great solution, I have been looking for this for a while – I am happy finally I got it …

In the first place i don't understand why developers find it difficult to include an aggregate function that should combine Text fields in group by (',' or ';' or 'space' or whatever) just like sum function adds up all numeric fields in a group.

The above solution is very thoughtful and works very well, but the last trick in the procedure doesn't always results in the desired output; i don't understand why we should use this trick instead we can reset the concatenate process in each [Line].

[VAR Concat Category] = [Category] +", "+ Previous(Self;([Lines]))

This will rest the concatenate in each [Line], so the last (max) running concatenate could be taken for each LINE without the need for the last variable in the above solution [VAR Category]

Former Member
0 Kudos

Adding a link was more helpful...http://bi.srivatsakr.com/2011/08/converting-rows-into-single-cell-comma.html

one should not take credit of what she has not done..

Former Member
0 Kudos

Hi Jothi,  Having trouble adding an image - what formats does this forum take?

Thanks,

John

jyothirmayee_s
Active Contributor
0 Kudos

Hi John,

I usualy go wiht JPEG... I think it should work

Thanks,

Jothi

former_member188911
Active Contributor
0 Kudos

PNG is better for zooming without blurring

jyothirmayee_s
Active Contributor
0 Kudos

Thanks Simone..

Can we also attach any documents..?. I was trying with Word and PDF but it doesn't show any option.

Thanks,

Jothi

former_member188911
Active Contributor
0 Kudos

it is possible to publish only the formats that you see in the options, this to avoid security issues..

thanks

Former Member
0 Kudos

Hi John,

You may also try to use these option to format the cells :

In the  Properties pane: check "autofit width" and "autofit height" and  then "wrap text" boxes .

I hope it helps.

Fadoua

jyothirmayee_s
Active Contributor
0 Kudos

hi,

What is the structure of your report(rows and columns)?.

Post the screen shot of what you are looking for?

Thanks,

Jothi

Former Member
0 Kudos

Hi Jothi,

See the Countries box below.  The problem is the countries come from multiple records (shown lower down under Variable Text) and I haven't figured out how to aggregate the countries all into a single string.  If they were in a single string I could use autofit height.   Alternatively, I tried horizontal tables but could not figure out how to get them to wrap.

Thanks,

John