PowerPivot with Oracle Functions (Excel 2010)

[ss_2014]

Hi All,
I have a requirement of building reports using Oracle functions in PowerPivot.
My Oracle functions are like below:
CREATE OR REPLACE TYPE <DATABASENAME>.obj_Inactive_Cases
AS OBJECT
(
STATUS VARCHAR2(30),
SUB_STATUS VARCHAR2(30),
CNT NUMBER(12)
)
/

CREATE OR REPLACE TYPE <DATABASENAME>.tbl_Inactive_Cases
AS TABLE OF obj_Inactive_Cases
/

/* ----------------------------------------
Object and Type for Report -
FUNCTION getInactiveCases(
RegisteredDateFrom VARCHAR2,
RegisteredDateTo VARCHAR2)
RETURN tbl_Inactive_Cases
---------------------------------------- */
FUNCTION getInactiveCases(
RegisteredDateFrom VARCHAR2,
RegisteredDateTo VARCHAR2)
RETURN tbl_Inactive_Cases
IS

INACTIVE_CASES_TBL tbl_Inactive_Cases := tbl_Inactive_Cases();

CURSOR INACTIVE_CASES_CUR IS
SELECT TBL1.STATUS
,TBL1.SUB_STATUS
,COUNT(*) CNT
FROM (
SELECT TBL.RECORD_ID, MAX(WCS.ID) ID
FROM <DATABASENAME>.TABLE_STATUS TBL
WHERE TRUNC(TBL.START_DT) >= TO_DATE(RegisteredDateFrom,'dd/MM/yyyy')
AND TRUNC(TBL.START_DT) <= TO_DATE(RegisteredDateTo,'dd/MM/yyyy')
GROUP BY TBL.RECORD_ID
) QueryResult
LEFT JOIN <DATABASENAME>.TABLE_STATUS TBL1 ON (QueryResult.ID = TBL1.ID)
GROUP BY TBL1.STATUS, TBL1.SUB_STATUS;

BEGIN
FOR INACTIVE_CASES_REC IN INACTIVE_CASES_CUR
LOOP
INACTIVE_CASES_TBL.EXTEND;
INACTIVE_CASES_TBL(INACTIVE_CASES_TBL.LAST) := obj_Inactive_Cases(INACTIVE_CASES_REC.STATUS, INACTIVE_CASES_REC.SUB_STATUS, INACTIVE_CASES_REC.CNT);
END LOOP;
RETURN INACTIVE_CASES_TBL;

-- End function
END getInactiveCases;
I have tried using Table Import Wizard and Command Type=StoredProcedure in th ePowerPivot.
Any help will be appreciated.
Thanks and Regards,
SS

 

[scottsen]

I have no direct knowledge here, but does this sorta imply you are in trouble?
PowerPivot - Oracle Stored Procedure err- The SQL statement is not valid. There are no columns detected in the statement.

 

[ss_2014]

Hi Scott,

As per the below link, PowerPivot doesn't support Oracle StoredProcedures/Functions. In order to generate report using these Stored Procedures and Functions, we have to use SSRS.

http://social.technet.microsoft.com/Forums/en-US/22e67f7f-0753-4f21-bae2-1812d2daf03e/powerpivot-oracle-stored-procedure-err-the-sql-statement-is-not-valid-there-are-no-columns?forum=sqlkjpowerpivotforexcel

I m not sure whether there is any other possible way to use Oracle Functions inside the PowerPivot.

 

[scottsen]

Ya, that looks like the same thread I posted, and it looks... unhappy for you. I know very little about Oracle, so can't really help out. You might see if Power Query helps you at all though? Otherwise you are probably looking at exporting from Oracle to CSV, then important that into Power Pivot.

 

[ss_2014]

Thanks a lot Scott.

Sorry I am very new to this Oracle plus PowerPivot functionality. Can you please help me in providing steps to do above mentioned thing? We actually deploy all powerpivot data to the SharePoint site to generate Power Views. I am not sure how to import data into PowerPivot using Oracle functions.

Need urgent help as we have to provide approach to the client ASAP