Stored Procedure in WebI Reports on BOE Part 1

How to use Stored Procedure on Web Intelligence (WebI) reports on Business Objects XI R2 ?

I found the following info in Bob forum and elsewhere on the net.

STEPS:

Applicable to universes that are built with ODBC or OLEDB connections.

[1] Locate the ODBC.SBO or OLEDB.SBO files

[2] Usual Location:

DRIVE:\BusinessObjects Enterprise 11.5\win32_x86\dataAccess\connectionServer\

DRIVE = your installation drive - be it C:\ , D:\ or something else.

/*

So, if BOE is installed in your D:\ drive then, the ODBC and OLEBD SBO files will be located in the following location:

D:\BusinessObjects Enterprise 11.5\win32_x86\dataAccess\connectionServer\odbc

D:\BusinessObjects Enterprise 11.5\win32_x86\dataAccess\connectionServer\oledb

*/

[3] Change parameter Force SQLExecute in "ODBC.SBO" to "Always"

[4] Add parameter Force SQLExecute = Always into OLEDB.SBO

> You can do the same on on the OLEDB.SBO file if need be

[5] Create query in WebI with the same structure your SP returns

> Same structure = same data types and equal number of columns

[6] Change SQL to following

Change your code from the WebI Query Panel > SQL > Custom:

SET NOCOUNT ON -- add exactly like this

/*SELECT any_field FROM any_table*/ -- this is the code generated by WebI

EXCE Your_Stored_Procedure @prompt('enter value','A',)

-- this is your stored procedure

That looks simple. Now let's have a quick walkthrough. Stay tuned for Part 2 ...