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.

Blog at WordPress.com.