Skip to Content
avatar image
Former Member

Rows to columns in BO webi

Hi All,

I have a requirement where i have to convert my rows into columns(different obviously :) )

So my data is

Id Region City

2 NAM NYC

2 NAM Washington

I have to display like this:

Id Region City1 City 2

2 NAM NYC Wash..

Is it possible to do it in webi?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Jun 16, 2017 at 01:26 PM

    Hi Akshat,

    Yes, its possible, try this https://blogs.sap.com/2015/08/15/string-aggregation-on-webi-report-level/. That logic fails sometimes when there is lot of data, in such cases, you can implement it in the Universe, try this blog https://irfansworld.wordpress.com/2011/09/15/how-to-display-column-values-in-a-single-cell-in-web-intelligence-using-oracle-database/

    Thanks,
    Mahboob Mohammed

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jun 16, 2017 at 01:49 PM

    HI Mahboob,

    Yes i was able to achieve that somehow :)

    but stuck in identifying the issue to divide comma separated values into multiple columns :(
    any idea on that?

    eg:

    column

    abc,def

    to, column1 column2

    abc def

    Add comment
    10|10000 characters needed characters exceeded

    • Use the below sample logic

      FirstName = substr([Customer Name];1;pos([Customer Name];" ")

      LastName = substr([Customer Name];pos([Customer Name];" ")+1;len([Customer Name])-pos([Customer Name];" ")

  • Jun 19, 2017 at 02:59 PM

    new1 =

    Substring([String];0;Pos([String];",")-1)

    than create new string without the first string (new1) and repeat till you have all your columns

    Add comment
    10|10000 characters needed characters exceeded

  • Jun 16, 2017 at 02:28 PM

    Hi,

    check out

    http://bi.srivatsakr.com/2011/08/converting-rows-into-single-cell-comma.html

    try to set every variable as a column,

    it thatis not working, you can create multiple colums with the same string in it, but you need to fetch the substrings between the ";" by getting the length of strings between the ; and each time show the string in one variable and remove the same string in the next variable and repeat that

    Grtz

    Koen

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      issuee.png

      Hi Koen,

      Please have a look at the screenshot
      the attribute Var Max Concat category is coming correctly

      but if i try to modify it to "new"

      i am getting extra "," which is creating problem :(

      any idea to resovle it.

      formula for new: =If(Left([VAR Max Concat Category];1)=",") Then "" Else [VAR Max Concat Category]

      issuee.png (24.0 kB)