Irfan's World

September 15, 2011

How to display column values in a single cell in Web Intelligence using Oracle database ?

A similar post has been posted earlier using SQL Server here.  The steps for creating such a report are same for Oracle database.

I am using Oracle 11.2.0.1.0 with Business Objects XI 3.1 – Version 12.3.0.601 on Windows 2003 O/S.

Requirement is to the DeptNo and all its employee names in a single line by each department number.

Create a dimension object in the Universe and name ColumnValue as below:

image 

Create a dimension object as a Delimiter for separating each value as below:

image

Next, Create the actual dimension object EmpName – SingleLine that you want to display on a single line in Oracle as below, In this example I am using the ename column from emp table in scott schema:

image Set the properties of EmpName – SingleLine Object as shown below:

image

The WebI report generates the following SQL

image

When refreshed the report displays the following results.

image


To remove the last comma use the LEFT function from Web Intelligence tool and extract only LENGTH – 1 characters.  That is all for today.

Advertisements

8 Comments »

  1. I have done exactly the same…….
    but i am getting repeated results.

    Instead of value1,value2,value3.
    I am getting like value1,value2,value3,value1,value2,value3,value1,value2,value3,value1,value2,value3.
    its repeating 4 times in a single cell.

    so is there any other solution to rectify this prblm

    Comment by Nik — March 11, 2012 @ 4:39 pm

    • Nik,Did you resolved this issue.Please let me know.I am having the same trouble .Repeated results when added few other object to the query in webi.I have two strings .resolved to some extent when crated multiple queries.

      Comment by Buggi — July 24, 2012 @ 1:57 pm

      • I am also getting the same issue with values repeating. How do I resolve it? (FYI, I am using BO 4.1)

        Comment by Naveen — February 6, 2015 @ 11:29 am

  2. We are using DB2 as our database and i was able to implement this issue.

    For removing the trailing “COMMA” i have defined the formula =Left([EmpName – SingleLine];Length([EmpName – SingleLine])-1)

    The illustrate the issue i am referring to the same example as stated in this post.

    Lets say we have Dept 10, 20, 30

    Also, employees in Dept 10 and 20 are the same.

    So when i view the table in Webi Report the result is something like this.

    Dept ID Emp Name
    10 Clark, King, Miller
    20 Miller, King, Clark
    30 Allen, Ward, Martin

    Why is this happening? Is their any solution for this problem?

    Comment by Adnan — May 3, 2012 @ 10:57 am

  3. Irfan,

    Is it possible to define the measure object “EmpName – SingleLine” so that the end SQL of the report would have a GROUP BY clause? This is what i have done.

    In our DB2 database i have created a Measure object as

    LEFT(VARCHAR(XML2CLOB (XMLAGG (XMLTEXT (ESADBM.COUNTY.COUNTYNAME || ‘, ‘)))), LENGTH(VARCHAR(XML2CLOB (XMLAGG (XMLTEXT (ESADBM.COUNTY.COUNTYNAME || ‘, ‘))))) – 2)

    When i check the SQL of the report it does not have a GROUP BY clause. What kind of aggregate can i use that would allow me to have this GROUP BY clause?

    Thanks,
    Adnan

    Comment by Adnan — May 10, 2012 @ 7:42 am

  4. Hi Irfan,

    This works perfectly fine when you have 1 column you want to combine into one row, what if you want to introduce another column(different field)that has 1-M relationtionship just like it on the same report?

    I tried the same method to create another ‘measure’ but when I have both ‘measures’ on the same report, the output duplicates itselfs for example : ‘Break/Fix, Break/Fix’ for column A and ‘Developed, Developed’ for column B using the same PK.

    i.e:

    PK COL_A COL_B
    1 Break/Fix, Break/Fix, Developed,Developed,
    2 Break/Fix, Tested, Break/Fix, Tested, Developed, Approved, Developed, Approved,
    3 Tested,Tested, Null

    When really this is how you want it too look like:

    PK COL_A COL_B
    1 Break/Fix, Developed,
    2 Break/Fix, Tested, Developed, Approved,
    3 Tested, Null

    Basically, I’m using the method you provided, created two different measures using them on the same report, and it’s producing bad data.

    Comment by Justin — February 21, 2013 @ 3:34 pm

    • I have not tested with multiple objects, may be you need to create individual data providers and test it or select Multiple SQL Statements for each measure option on the universe.

      Comment by Irfan — February 24, 2013 @ 9:25 am

  5. In Query Property uncheck ‘Retrieve Duplicate Rows’. This did the trick for me in eliminating repeating values. Hope this helps.

    Comment by NRIGirl — September 3, 2014 @ 7:47 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: