How to Pass Store Procedure Query Parameter in PowerBI Report Builder Using ODBC Connection

Hi looks like you have some details to use in the link you provided, but for some it may not be detailed enough, so a step for step would be (for myself I make sure I can execute the stored procedure in SMSS first, if it has parameters you need to know what they are ie: txt, date or numeric, as you will need to specify this when calling it in Power BI Report desktop app also..).

So here goes: (assuming you are using a Start/End date parameter in the stored procedure)

1) (in Power BI) - open a new "blank query" (Home - get data - blank query)

2) when query open click on "Advanced editor" (Home - advanced editor)

3) Paste this in: (make sure you know the parameter name of course, also we dont use end dates - so you'd want to adjust the parameters in the second query below to include this..  🙂 .

let

SQLSource = (pStartDate as date) =>

let

Source = Sql.Database("Server\Name", "Database_EpiReporting", [Query="exec sp_PBI_APP115_stored_proc_name

 '"& Date.ToText(pStartDate) & "'  "])

in

Source

in

SQLSource

OR Date and text below.....

let 

SQLSource = (pStartDate as date, pCensusFrequency as text) => 

let 

Source = Sql.Database("Server\Name", "Database_EpiReporting", [Query="exec sp_xtr_stored_proc @pStartDate = '"& Date.ToText(pStartDate) & "', @pCensusFrequency= '" &  pCensusFrequency  & "'"]) 

in 

Source 

in 

SQLSource

Once the query is edited you'd click on OK or apply and next you would get a prompt to enter the username/password for the account (we use server accounts - but htats very straight forward)..

After applying the access you would be left with the paramter boxes - in my above instances it would be a date drop down selector for start date, or a start date drop down and a text field - wher eits expecting (for us) a Hospital name.. Once applied you'd get through to your data... hopefully 🙂

Good luck - dont forget to post your fix back here, always good to contribute where possible IMO

Cheers

ClinEpi