topic Re: How to use stored procedure to get data? in Report Server

How to use stored procedure to get data?

SJi — Fri, 12 Jan 2018 16:41:13 GMT

Hello,

we have stored procedures made for our mobile and paginated reports and we would like to use them with PowerBI. The procedures often have temp tables and parameters. I've enabled opequery on our database, however I'm getting an error as show below. How can be stored procedure used to get data in PowerBI? Any help would be appreciated.

Details: "Microsoft SQL: The metadata could not be determined because statement 'SELECT 
		ROW_NUMBER () OVER (PARTITION BY a.MonthBack ORDER BY sum(a.ApprovedLoanAmount) DESC)	
AS' in procedure 'MR0001_MosRanking' uses a temp table."

Re: How to use stored procedure to get data?

SJi — Sun, 14 Jan 2018 21:08:56 GMT

I've found there're basicaly 3 ways how to utilise stored procedure in Power BI:

Using and executing variable
Openquery
Table-Valued Function

It seems, the most straightforward way is to execute variable which stores stored procedure call, however I'm having some troubles in doing so.

Re: How to use stored procedure to get data?

v-yulgu-msft — Mon, 15 Jan 2018 01:57:58 GMT

Hi SJi,

Thanks for your sharing.

Regards,

Yuliana Gu