Stored Procedure in WebI Reports on BOE Part 1

| June 24, 2009
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 …

Tags: ,

Category: Business Objects, SQL

Profile photo of LetsLearnBI

About the Author ()

Comments (1)

Trackback URL | Comments RSS Feed

  1. tim says:

    I tried the solution you suggested, but the query comes back with "Error on Bindcol". The Stored Procedure I get the error on creates temp tables. Other, simple Stored Procedures seem to work fine. Any thoughts?