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:

 

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.