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 with Business Objects XI 3.1 – Version 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:

image 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.


March 5, 2011

Differences between Crystal Reports XI R2, Web Intelligence XI R2 and Web Intelligence XI 3.0

A quick reference sheet that shows the differences between BusinessObjects reporting tools.


January 27, 2011

Custom Date Format in Universe Designer for WebI reports

Date is an important attribute in doing business, capturing events etc.   As you know there are lot of different date formats used in different countries.  For example, In USA we use MM/DD/YYYY format, in UK they use DD/MM/YYYY etc.  A request came to me from one of my client to format a date that displays month name, something  similar  as "7th of February 2005"  or "23rd of February 2004" format, as they need to convey some information through a business letter to their customers living around the world.  The client’s reporting system mostly uses Web Intelligence reports.   My first idea was to train the users on how to create variable in the report and use it, Later I have decided that I should create an object in the Universe and came up with the following solution.  It is not that difficult once you understand what the code does to create a Universe object to support this requirement.

We have the following data in the SQL Server 2008 database, which shows the date format from the database.

SQL Code for the Universe Object (SQL Server) :

WHEN DATENAME(DAY, [Date]) IN (1,21,31) THEN
DATENAME(DAY, [Date]) + 'st ' + 'of ' +
DATENAME(MM, [Date]) + ' ' +
DATENAME(DAY, [Date]) + 'nd ' + 'of ' +
DATENAME(MM, [Date]) + ' ' +
DATENAME(DAY, [Date]) + 'rd ' + 'of ' +
DATENAME(MM, [Date]) + ' ' +
DATENAME(DAY, [Date]) + 'th ' + 'of ' +
DATENAME(MM, [Date]) + ' ' +

Parse the Universe Object to make sure that the syntax and the code is correct, test the object on few reports before it goes to production.

Let’s test what data is returned by this object.

Hope this tip helps. See you later !!!.

Blog at