Skip to Content
avatar image
Former Member

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

Oct 01, 2012 at 08:09 PM | 723 Views

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

SAP BusinessObjects - Web Intelligence (WebI)
Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

10 Answers

  • Best Answer
    avatar image
    Former Member
    Oct 02, 2012 at 04:39 PM

    Hi Jothi,

    Add comment
    10|10000 characters needed characters exceeded

    • Swapna SM Sukumar Budala
      Aug 11, 2016 at 11:47 AM

      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

  • avatar image
    Former Member
    Apr 04, 2013 at 08:23 AM

    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.. 😊

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jul 29, 2015 at 11:28 AM

    use ReportFilter([State])

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Jyothirmayee A
    Oct 01, 2012 at 10:14 PM

    hi,

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

    Post the screen shot of what you are looking for?

    Thanks,

    Jothi

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member
      Oct 02, 2012 at 07:27 PM

      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

      LSA.JPG (81.3 kB)
  • avatar image
    Former Member
    Oct 02, 2012 at 05:01 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 02, 2012 at 06:23 PM

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

    Thanks,

    John

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Apr 29, 2013 at 07:00 AM

    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]

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Apr 15, 2014 at 12:27 AM

    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.




    Previous(Self).png (15.2 kB)
    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 10, 2014 at 05:14 AM


    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


    pastedImage_0.png (18.6 kB)
    pastedImage_4.png (9.0 kB)
    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Nov 20, 2014 at 06:05 PM

    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?

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member
      Nov 20, 2014 at 07:15 PM

      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]