Forum Discussion
How to Pass Store Procedure Query Parameter in PowerBI Report Builder Using ODBC Connection
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
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
2 Replies
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.
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.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
