Monday, January 10, 2011

OBIEE Using a MSSQL stored procedure as datasource adding a variable:

If our stored procedure uses a variable:

[code]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        John Minkjan
-- obiee101.blogspot.com
-- =============================================
ALTER PROCEDURE sp_dim_Accounts   
@UserName varchar(50) = 'me'
AS
BEGIN
    SET NOCOUNT ON;
    SELECT     dim_Account_ID, Account_Code, Account_Name, @UserName as USERNAME
FROM         dim_Accounts
END
GO

[/code]

You can add the variable by using the VALUEOF() procedure:

 image

@VARIABLENAME = N’VALUEOF(NQ_SESSION.VARIABLE_NAME)’

Till Next Time

4 comments:

Anonymous said...

Hi

I am looking to created a cloumn header dynamically i.e if its Jan to Jun the col heading should be H1 forecast and from Jul to Dec it should be H1 Actual. How do I achive this? Reuest you to kindly help on this

ram said...

Hi

What is the difference between oracle discoverer reporting tool and OBIEE.

Intrgrating the OBIEE reporting tool to ERP systems lie Oracle Apps,Peoplesoft and siebel is easy.

absarforex said...

good information on this blog.nice job

Unknown said...

Hi, does anyone know if the above solution works for 'select' option instead of the 'Stored Proc' option?