How to fetch output of Stored Procedure in EJBI during Cube Creation : 

 

A) MSSQL Server  : 

 

1) Stored Procedure Without Parameter : 

 


2) Stored Procedure With Hard coded parameter (e.g. bill number): 




3) Stored Procedure using Global variable : 

 




B) Oracle : 

 

Syntax for executing SP  through Oracle Data Source Connection is as below :


{call BI_CUSTOMERCOMPLAINT(:b,3,'a','25/10/2016')}


 CREATE OR REPLACE PROCEDURE BI_CUSTOMERCOMPLAINT

(    

 cv_1 OUT SYS_REFCURSOR,     

  v_DocumentId IN NUMBER,

  v_DocumentId2 IN varchar2,

  v_date in  date

)

AS

BEGIN

   OPEN cv_1 FOR

     select * from VIEWCUSTOMERCOMPLAINT;    

 END;

 

Note : In case of Oracle it is mandatory  to have Sys Ref Cursor.  Output has to be captured in SP and then can be used to create ElegantJ BI Cube.