SQL Stored Procedure with 2 result sets

[Anonymous]

I need to create 2 table reports on the page. Backend is SQL Server database which is being accessed via stored procedure due to permissions for data access.

Table report one selects 3 columns from a SQL Server table .  Table report 2 filters dataset from Table report 1.

Use some sort of dataset using a stored procedure where #Report1Dataset I plan to use for Report1 and #Report2Dataset to use for #Report2. I do not need to create a tabular model, basically this is all I need.

“

Create proc usp_filterData

As

begin

Select col1, col2, col3

Into #Report1Dataset

from TableName;

Select col1,col2, col3

Into #Report2Dataset

From #Report1Dataset

Where col1 like ‘%Pattern_1%’

Or col1 like ‘%Pattern_2%’

….

Or col1 like %Pattern_N%’;

select col1, col2,col3 from #Report1Dataset; --first report on the page

select col1, col2,col3 from #Report2Dataset; --data for this second report is related from first report

End;

“

I am new to Power BI, used SSRS in the past.

Can you show me the idea?

Thank you,

[v-deddai1-msft]

Hi @Anonymous ,

Based on your description, you want to get data from sql server by  stored procedure, right?

You can achieve by  Advanced Options ,paste the query in the SQL Statement that opened up. CheckNavigate using full hierarchy and click on OK.

If you want to pass parameter to stored procedure, please refer to  https://www.c-sharpcorner.com/article/execute-sql-server-stored-procedure-with-user-parameter-in-power-bi/

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Anonymous]

Hi Dedmon,

I know how to pull data from SQL proc.

Getting the resultset for the first report on the page is clear, but the second report resultsetthen filters the first resultset (from first report) by using a "like" comparison in SQL as per my procedure example in original post.

both queries are pretty expensive for SQL db.

Thank you,

[Anonymous]

Can anybody help?

basically I need to run one stored procedure, and get 2 resultsets both of them in a separate table report on the page.

Ex: if I run one stored procedure in SQL Management Studio then I can get 2 resultsets (or even more). 

Is there any way to do this in Power Bi?

Thank you,