Stored Procedure in WebI Reports Part 2

WALKTHROUGH: Using Stored Procedure in WebI reports on Business Objects EN XI R2 (SP3)

This is a follow up of Part 1 where we went through the prerequisites of enabling stored procedure queries on WebI.

We have a simple Stored Procedure called "uspGetStates" - which is intuitive and does exactly what the title says - it gets names of states.

Stored Procedure: dbo.uspGetStates

Running the procedure: EXEC dbo.uspGetStates will give you results like this:

(partial result)

AA 53

AE 52

AK 1

..

..

so on (see pic)

This store procedure returns 2 columns - one with 'string' datatype and the other with 'number'.

If you can recall that your WebI query panel must have the same number of columns - i.e. Dimensions and same number of corresponding data types as your stored procedure does. Else your procedure won't validate, and thus won't work on WebI.

[1] So, we run the procedure and got some result

[2] We have to create a WebI Report with the same structure as our procedure returns

[3] Use the codes:

a) "SET NOCOUNT ON" on top of the code generated by WebI

b) Comment out - but, DON'T delete your WebI code

c) At the bottom of the comment, use your store procedure - in this case we'll use: "EXEC dbo.uspGetStates"

[4] Uh oh ... Error ! Type mismatch. Let's reslove

[5] We used:

"Company" which has "string" datatype - good

"Saleareanum" which also has "string" datatype - no good

So, we'll use:

"Saleareaid" which has "number" datatype

[6] Having corrected the Dimensions' datatype and matching them with those of the stored procedure, we are ready to validate.

[7] Validation successfull !

[8] We run our WebI report and see that our report returns the same data as it return running the stored procedure on SQL Server Management Studio.. (null row is displayed on WebI as the last row).

Stay tuned for Stored Procedure with Prompts in Part 3 ...