Saturday, June 27, 2009

Stored Procedure in WebI Reports Part 3


Image walkthrough: Stored Procedure with Parameters / Prompts in WebI Reports


[1] We are starting with this WebI report which was created with a simple stored procedure, in Part 2: That stored procedure just returned 2 columns. Now, we are going to work with a stored procedure with 2 input parameters, aka 'prompts' (in WebI). Very useful.



[2] Our 2nd stored procedure returns 2 columns, based on users' response. The parameters are 'StateID' and 'StateAbbrev' - with datatype of number and string / varchar, respectively. And since we know where these 2 fields are from, a quick way to test this procedure would be to find and enter 2 valid parameters when executing the procedure, in this case, we'll pick '3' for state 'AR'.

Let's run the procedure:
Usage format:

EXEC Stored_Procedure StateID, StateAbbrev

EXEC Stored_Procedure 3, AR

> see results of this procedure at the bottom of the following pic



[3] So, back to WebI > SQL (View SQL) > Use Custom SQL:

We'll apply our store procedure: EXEC Stored_Procedure 3, AR

make sure, to use "SET NOCOUNT ON" and comment out (/* blah blah */) the codes generated by WebI - do NOT delete the codes.

Validation successful.



[4] Run it and the report runs. So, we got it to work. Don't worry about the column headers, you can rename them to suit your needs.

Now, let's create prompts.


[5] We'll use both types to be Alphanumeric

This your basic @Prompt syntax:
@Prompt(‘message’,‘type’,[lov],[MONO|MULTI],[FREE|CONSTRAINED])

So we have

-- EXEC Stored_Procedure 3, AR (Original format)

EXEC Stored_Procedure
@Prompt('Enter STATE ID' , 'A' , MONO , FREE , PERSISTENT) ,
@Prompt('Enter STATE ABBREVIATION' , 'A', MONO , FREE , PERSISTENT)

... And validation successful. Great.



[6] As we save and run the above modified WebI query, we are prompted - perfect! So, we'll once again enter '3' and 'AR' to match with the results we got when we ran this procedure on SQL M our result in SQL Server Management Studio.



[7] So, now let's run this stored procedure with prompts on WebI - bingo, Success!


[8] NOTE:

If you go back to the code, you'll see that the @Prompts are gone in custom SQL query. Don't worry, simply "close" the code and the report will still prompt you. BUT, if you hit "save", then it will only retain the value you see - in this case '3' and 'AR'.


Thursday, June 25, 2009

Scope of Analysis and Custom Hierarchies

Video: Demystifying Scope of Analysis and Custom Hierarchies on Business Objects XI Universes and WebI / DeskI reports.

The terms "Scope of Analysis" and "Custom Hierarchies" sound so complex and sophisticated that one would think that wow... Business Objects got some very advanced futuristic features. I, of course do not deny that these are amongst the most useful features of BOE, but they could've used simpler terms, like 'custom drill down' or 'drill down settings' - sounds much friendlier and less terrifying.

If you are in the field of Business Intelligence - using any BI tool, you've probably come across drill downs in reports. Well, in BOE, drill down options, or custom hierarchies, as they call it, is defined in Universes - which in turn, reflect in DeskI or WebI reports.

For example, you may be creating a sales report and your database has records for last 5 years, by year, quarter, month and so on. By default, drilling down on this report would let the users dynamically view the results like this:

Level 1:

Year Sales
2009 500

Level 2:

Quarter Sales
3 150

Level 3:

Month Sales
11 70

...and so on.

But, let's say your CIO is either lazy or simply doesn't care about the quarters and would like to drill down from Year level to Month level to Week level - skipping Quarter level. This customization of drill down is what is known as 'custom hierarchies' in a BO universe and when it is used in a WebI / DeskI report, it is called the mysterious 'scope of analysis'. Wow, big deal...

Let's see a quick video walkthrough:







Wednesday, June 24, 2009

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

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