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

[AshutoshSahoo_1]

Hello Experts,

                      I'm connecting amazon aurora MySQL and using an Odbc connection for fetching data, My Issue Was 

                     when I'm passing the query parameter the store procedure referring from this Link Reference_Link  but it's

                     still not working.

                      Please give me a proper way of calling The store procedure to pass the query parameter.

thanks & regards

Ashutosh sahoo

[Clinical_Epi · Accepted solution]

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

[v-henryk-mstf]

Hi @AshutoshSahoo_1 ,

Do you have the appropriate gateway configured for the ODBC data source?
For common debugging problems with query parameters you can refer to the advice given in the following document.

Power BI gateway and Report Builder support for ODBC data sources (preview) - Power BI | Microsoft L...

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

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