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.