Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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,
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
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,
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,