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:
Create a dimension object as a Delimiter for separating each value as below:
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:
Set the properties of EmpName – SingleLine Object as shown below:
The WebI report generates the following SQL
When refreshed the report displays the following results.
To remove the last comma use the LEFT function from Web Intelligence tool and extract only LENGTH – 1 characters. That is all for today.
Irfan's World 
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
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
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
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
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