The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi All,
I am trying to connect a store procedure from SQL Server to Power Query and have an option to contol its parameters through Power BI Slicers. However, if I want to use binding parameters, I have to connect Store Procedure to Power Query at "Direct Mode". That is the reason I use OPENROWSET . Everything looks good in Power BI Desktop, but when I publish it to service. Dashboard is unable to find its dataset. Any suggestion?
let
startStr = Text.From(START_PRAM),
endStr = Text.From(END_PRAM),
Source = Sql.Database("Prod64", "AAA_DWH", [
Query = "SELECT * FROM OPENROWSET('SQLNCLI', " &
"'Server=Prod64;Trusted_Connection=yes;', " &
"'EXEC [dwh].[RPT].[RPT_PO_VO] @START_DATE_SP=''" & startStr & "'', @End_DATE_SP=''" & endStr & "''')"
])
in
Source
Hi @Adam_2024,
I could offer you two ways to solve the problem:
1: Use DirectQuery with Parameters (Preview Feature)
let startParam = Parameter.Text("Start Date"), endParam = Parameter.Text("End Date"), Source = Sql.Databases( "Prod64" ){0}[dbo].[RPT_PO_VO](startDate = startParam, endDate = endParam) in Source
2: Use a Staging Table
You can try both of them and find which works for you.
If you find this insightful, please provide a Kudo and accept this as a solution.
Hi @Shivu-2000 Many
thanks for providing some advice,
I am happy to get more info regarding your first solution as I am curious to see whether I can query a store procedure by changing its parameter in Power BI Slicer like Tableau.
My Power BI Desktop version is the latest one ( 2024 - April). However, Preview feature is not availbe in Current File, instead in Global Section. I also looked for Preview feature in Global Section for DirectQuery with Parameters and there is no option available for this feature. Any suggestion?