Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AshutoshSahoo_1
Frequent Visitor

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

1 ACCEPTED SOLUTION
Clinical_Epi
Advocate II
Advocate II

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

View solution in original post

2 REPLIES 2
Clinical_Epi
Advocate II
Advocate II

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
Community Support
Community Support

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.

vhenrykmstf_0-1668073332191.png

 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.