Wednesday, June 24, 2009

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

1 comment:

  1. 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?

    ReplyDelete