Irfan's World

November 17, 2012

What you should know about Stored Procedure Universe in BI 4.0 ?

As we all know there are many advantages of using stored procedures, they are always in my problem resolving kit, as a last resort I have to use it to create one of the complex report that needs to be delivered  as Web Intelligence report. Apart from providing many advantages like transaction control, speed, complex logic support, security and data access, they are powerful enough to deliver complex reports.  My all time favorite tool is to use Universe, I have to use stored procedure to develop a report that takes data from multiple excel files including few database tables,  one other thing I would like to mention is the data from excel file changes everyday.  Luckily Business Objects supports stored procedure universes and I have used it to deliver this requirement.

I would like to bring my experience to all my readers who are interested in learning what stored procedure universe can offer to a developer or an end-user. I would like to point out what is possible and what is not possible with Stored Procedure universe and I hope it should give a clear understanding when one can utilize it.  I am using BI 4.0 with Oracle 11g database.

What is a Stored Procedure?: Sets of SQL statements that are saved as executable files in a database fully parsed.
What is a REF Cursor?: A pointer to a work area in which a result set is stored.

What are the other requirements needed at first place ?

  • Create package that defines the REF CURSOR. This REF CURSOR must be strongly bound to a static pre-defined record structure.
  • The procedure must have a parameter that is a REF CURSOR type.
  • The REF CURSOR parameter must be defined as IN OUT (read/write mode).
  • The stored procedure can only return one ResultSet
  • Test and run the stored procedure in an SQL Tool like TOAD or SQL*PLUS

Once all of the above requirements have been met, verify that the database driver that you are using works with that version of Oracle.

I am using SCOTT schema from Oracle.  Following is a package emp_package declaration for a strongly typed cursor in Oracle using EMP table.  REF CURSOR emp_row_type returns a result set of type emp%rowtype


Following is a structure of the stored procedure, it shows how to use or refer emp_row_type REF CURSOR data type declared in the emp_package.


The name of the stored procedure is getEmployeesByDepartment, It accepts a parameter value for a department column and returns the data from the EMP table. We will use this stored procedure to build a universe in Business Object. First create a secure connection that connects to the database and also make sure you have selected the checkbox ‘Click here to choose stored procedure universe’ as shown in the screenshot below. This will indicate that it is a stored procedure universe


If stored procedure has parameters, it will now ask to enter information for the Parameter, Prompt Label and what it should do on ‘Next execution’ ?. If you see the screenshot below it shows value column, prompt label and Next execution drop down box. Click OK to save and close this window.


Once all the information is provided, it will display a table in the universe pane as shown below, this table contains all the columns that will be returned by the stored procedure. In this example, I have created a class named Employee with two measure objects like Sal(Salary) and Comm(Commission).  Similar to an ad hoc universe one can use the stored procedure without an issue.  There are no joins, no contexts in the universe, all the business layer logic is stored in the stored procedure itself. 


You can save the universe and export it to the repository.  Now, you can use this stored procedure universe to develop Web Intelligence reports.  You can drag and drop the objects similar to any other Universe. There are few things that are not possible in query panel.  You cannot create additional prompts or add additional criteria in query panel. You can refresh and view the returned data from the database.


Now use the Universe to create a Web Intelligence report.  I have selected all the columns that were returned by the stored procedure.


What is possible in a Stored Procedure Universe ?

    You can change the data type of an object
    You can set object properties of an object
You can do the check integrity of stored procedure universe
    You can use stored procedure objects in result pane while creating report.
You can apply sort on an object if required.
You can apply access restriction for a user or group.
You can set a prompt value if the stored procedure accepts a prompt.

What is not possible in a Stored Procedure Universe ?

     You cannot use a stored procedure object in the criteria pane in query panel of Web Intelligence report.
     You cannot change the definition of a Universe object, it is disabled or grayed out by default.
     You cannot create a pre-defined filter using a stored procedure Universe object, it is disabled or grayed out in the Universe.
    You cannot INSERT another stored procedure in a stored procedure universe.
    You cannot INSERT a table in the stored procedure universe.
    You cannot call a stored procedure from a Derived Table in Universe.
    You cannot apply INDEX AWARENESS on stored procedure Universe objects.
    You cannot create custom or new objects that were created for certain purpose based on stored procedure objects.

In conclusion I would say that there are limitations of using Stored procedure universe, lot of things not possible at the Universe Level and the universe becomes like a read only Universe. There is a little control on the Universe side which is changing a connection such as ODBC to Native.  Would use this feature only as last resort.  If you have any comments please share it through the comments section below.

September 18, 2011

What’s new in Web Intelligence Rich Client in BI 4.0 ?

After installing the BI 4.0 and while exploring Web Intelligence Rich Client tool, I found some of the cool features from Classic are back and some are introduced new.  In brief, navigating in Web Intelligence Rich Client is by different tabs instead of menus which is similar to other tools of SAP.  What does it mean by navigating by tab ..?  Here is an example,  Like to add a new table, you need to click the Report Element tab and then click Table button, similarly if you want a new data provider, you need to click the Data Access tab and then click New data provider button.  It will take a while to get used to it….but definitely I like it.

There are also new features introduced and some features of Desktop Intelligence have been added too.  I have not yet explored the tool in full but definitely came across some good features. Lets find out what I have found so far ? 

 New data sources have been introduced to build a report

Apart from Universe and Excel file, Web Intelligence Rich Client now supports various other data sources like Bex Query, Analysis (Cube), a text file and Web Services. 


 Data preview in Query Panel.

To preview the data in query panel, you need to first select Data Preview Panel on the top, a separate Refresh button has to be used to refresh the data in the previous panel.  You can have a first look of the data itself in the Query Panel and judge whether your query is returning the right data or not.  If not then you can then try fixing it then and there.


  Desktop Intelligence data manager is back

You can see how many rows a data provider returned after it is refreshed from Data view button on top right as shown below.  It will also display the time in seconds that it took to refresh for those who wants to improve the performance of the query.



Hiding a column in a report block is back. This makes life much more easier.

You can hide a column by making a right click on a column, you will see a context menu that displays Hide option as shown in screen shot below.


 Hiding a section in report is made easy.

Similar to hiding a column, hiding a section is also possible by making a right click on the section column, here my section column is Year.   It shows Hide option with three choices Hide, Hide when Empty, Hide When..



  Multiple Input Controls with multiple values

If you see the screen shot below, you will see multiple input controls on the left that I have created, one with Radio buttons control that displays different quarters Q1, Q2, Q3 and Q4 in the report and the other is a Check Box control that displays different years 2004,2005 and 2006.  Data in the report is displayed as per the selected input control data values….. cool.



  Line numbers in Query Script Viewer (aka View SQL in Query Panel)

When you try to view the SQL generated by the data provider after adding the required objects and pre-defined filters, you will see line numbers in the SQL code as shown in the screen below.


That is all for today,  Hope you enjoyed the new features or might have gained some new insight in BI 4.0 Web Intelligence Rich Client.  Please do leave your comments, feedback and suggestions if any. Have a great weekend.  Thumbs up

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.

July 13, 2011

How to display more than 1000 values in a LOV created in Crystal report or Business View Manager ?

The following resolution involves editing the registry. To increase the maximum number of values available in a dynamic parameter list, you will add a registry key. Contact your Network Services to make changes to the system registry. Using the Registry Editor incorrectly can cause serious problems that may require you to reinstall the Microsoft Windows operating system and whole lot of other required stuff. Use the Registry Editor at your own risk.

It is strongly recommended that you make a backup copy of the registry files (System.dat and User.dat on Win9x/WinXP/2003/2003/Vista/Win 7 computers) before you edit the registry.

   Step 1. Create a registry key LOV under DatabaseOptions in the following path HKEY_LOCAL_MACHINE\SOFTWARE\Business Objects\Suite 11.0\Crystal  
.  See the screenshot below:



   Step 2. Add a string value "MaxRowsetRecords" and set the value to the maximum number of values that you desire for your report. For example, a value of 3000 will return up to 
      3000 values in the lowest level of a cascading LOV parameter. In the above screen a value of 60000 is set as it was required in the environment and it works perfectly fine.

The value 0 (Unlimited) will not work with BusinessObjects Enterprise XI or Crystal Reports Server XI, you must specify another value.

NOTE: After making changes to the registry, restart the affected service or application as required.  

May 14, 2011

How to create a filter in Web Intelligence document that reflects on multiple report tabs..?

In this post, I am going to explain how to create a variable filter in a Web Intelligence document, that when applied on the first tab reflects on all the other tabs in the document,  In other words, the changes are made in the local document variable at one place and the changes are applied to all the tabs where this filter is used in the document.   

Let’s say we have a Web Intelligence document with 4 different tabs and each tab with different grouping on dimensions.  Similar to the following star bullet points:

   Tab 1 – Displays Revenue by Year
  Tab 2 – Displays Revenue by Year & Quarter
  Tab 3 – Displays Revenue by Year, Quarter & State
  Tab 4 – Displays Revenue by Year, Quarter, State & City

I am going to demonstrate it by using the standard eFashion Universe.  After dragging the required objects, My query panel looks like below and note there is no query filter:

When I run the above query the following results are displayed from each year 2004, 2005 and 2006.

Create a document variable

Create a variable by name “?_Tab_Filter” and put the following code =If ([Year]="2004";1;0).  Next apply this filter to the first tab and choose to filter on “1” (i.e.  to display only those records where the value is 1).

Now copy this tab 4 times to deliver the four reports above and keep only the required objects and remove objects that are not required in each tab.

Tab 1
Tab 2
Tab 3
Tab 4

Remember that when you copy the first tab as different tabs for this kind of requirement, the variable that is added as a filter(?_Tab_Filter) will also get copied along with it to each tab in the document. In my case here, I want to filter on 2004 as my Year, Hence it displays data related to Year=2004 in all the tabs. I repeat all the tabs whether you have 1, 2, 3…. 50 tabs or more.  This works.   Cool

Now if you want to see the data for the Year = 2005 instead of 2004 in all the tabs, then edit the variable ?_Tab_Filter and change the code to =If ([Year]="2005";1;0) and click OK to modify the variable.  This change will reflect automatically on all the tabs where this filter is used and the data is displayed according to the value selected for the dimension Year, here I am changing the variable to 2005 from 2004.  All the tabs now displays data for 2005 including blocks if you have any.  Isn’t this feature is superb ? Yes it is.   Give it a try !!!


    One variable filters all tabs.
    Can be applied to all the blocks (table, crosstab, charts) in a tab.
    Additional dimensions can be added or removed to the filter.    (ex: =If ([Year]="2005" and [State]=”California”;1;0) )
    Assists in bursting the full document  or separate it individually so that all the tabs displays only filtered data.  (ex: a store report for each store manager)
    No manual intervention necessary to go to each tab and change the filter value.


  All the required data has to be in the report.
  User needs to change the filter values.   ( no drop down option …Sad, so some knowledge of WebI syntax is necessary)

     Without a doubt in my mind, I say that it is one among the powerful feature’s of SAP Web Intelligence.  That is all for today and thanks for stopping by.

April 23, 2011

Crystal Reports 2008: Failed to open the connection. test_oracle Details: [Database Vendor Code: 12154 ] – Resolved

Filed under: SAP Crystal Reports — Irfan @ 12:13 am

After spending lot of time on Google to fix the connectivity issue on Windows 7 64bit between Oracle 10g and Crystal Reports 2008 SP3, I thought I should write this post that may assist some of my readers including me for future reference down the road.  There is not much information available on the web and not any documentation about this issue.  The above error appears sometimes in Business Objects 3.1 SP3 Infoview or when you connect Crystal Reports 2008 Designer Tool to Oracle 10g database.

Remember the following steps:
a) Crystal Reports 2008 SP3 is a 32 bit application.
b) Business Objects 3.1 SP3 is a 32 bit application.
c) So, install  Oracle 10g 32 bit or 64 bit, But install Oracle 10g client connectivity software of 32 bit only to interact with Crystal Reports 2008.
d) If you want to use ODBC, you can connect using Microsoft ODBC drivers that are installed by default or you can also download Data Direct 5.3 drivers. or try this link

Following is my environment on which connection is successful:
Operating System:   Windows 7 Ultimate (64 bit)
Database:                Oracle 10g (32 bit)
Reporting Tool:       Crystal Reports 2008 SP3 (32 bit)
Enterprise:               Business Objects 3.1 SP3 (32 bit)

Using ODBC
1) create a 32 bit system DSN on the client machine (or the server).
    On a 64bit operating system, you will find 32 bit ODBC setup file in the following path [drive:/]Windows/sysWOW64/odbcad32.exe

2) Open Crystal reports designer tool, and try connecting to the System DSN created in step 1.  Check the following screenshot, Crystal Reports 2008 SP3, Connection tested with ODBC and it is successful on my machine.  Oh what a relief !!


2) Check the following screenshot, Crystal Reports 2008 SP3, Connection tested with TNSNAMES.ORA (i.e. Oracle Server)


Make sure that you install all the correct 32 bit versions of Crystal Reports 2008, Business Objects 3.1 and Oracle 10g to be compatible with each other. The report should work fine in Infoview and local PC.

Share your thoughts or any alternative solutions to resolve this error. Have a Good day.!!  Smile

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.


February 18, 2011

How to display a database column values in a single cell in Web Intelligence Rich Client ?

In this post, I would like to show you how to display an object value in a single cell separated by a delimiter in a WebI report.

I have a table name called Account in Xtreme database and it has the following values,  the requirement is to display all the account names in a single cell in the report.



This requirement can be fulfilled if we have access to Universe Designer in which three following objects needs to be created with the syntax shown, I am using SQL Server as my database.

    Column Value
    Actual Object Name – Single Line

1) Create a ColumnValue object as below:


2) Create a Delimiter object as below:


3) Create a Account Name object as below:

This step needs two things, make sure the definition of the object looks like below plus the qualification should be a measure with projection as SUM



Objects qualification and projection


All the ground work is done, now I will use the object in a sample report.  Let’s see what it returns ?


The report output is shown below…. voila !!!


That is all for today.Wave

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

Create a free website or blog at