cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Pass user input parameters from report page to SQL stored procedure

Hi guys,

 

I am currently trying to build a very dynamic report for my end users, and it would require something like this:

 

I create a page with one-or-more input field (slicer or something like that), and when the user uses/sets these input fields (slicer or etc.) then these parameters gets passed to an SQL stored procedure, and the result is then visualised for the user.

 

Is it possible in Power BI? If not this, than is there any way to give the enduser more freedom with my reports?

 

Thanks in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Pass user input parameters from report page to SQL stored procedure

Hi @Astorre72

 

Yes, create query parameters then go to data source setting>select the parameter you need and put your exec queries in Sql statement to get your SQL stored procedure,see below pics:

 1.PNGAnnotation 2020-07-22 091705.png

Here is a reference about how to use query parameter.

https://docs.microsoft.com/en-us/power-query/power-query-query-parameters#:~:text=To%20create%20or%2...

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

View solution in original post

4 REPLIES 4
Highlighted
Super User II
Super User II

Re: Pass user input parameters from report page to SQL stored procedure

can you give more detail? freedom to do what?




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Community Support
Community Support

Re: Pass user input parameters from report page to SQL stored procedure

Hi  @Astorre72,

 

Once you have granted access to the end users of your SQL data source,when they are connecting the data source in power bi ,they will gain the freedom to your report.

Below is the reference.

https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-enterprise-manage-sql

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-use-directquery

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Highlighted
Frequent Visitor

Re: Pass user input parameters from report page to SQL stored procedure

Hi @v-kelly-msft 

 

I think I should specify my question further. This is what I already know and tried out:

 

I can connect to my SQL data source with import or direct query mode. I can import or direct query SQL tables and views and even stored procedure. But what I would like to do is the following:

 

I want to write an SQL stored procedure with three argument: beginning date and end date and ID. If this three parameter is given to the stored procedure then it returns two columns of data: timestamp (between beginning date and end date) and data (for example stock settl. price). I can achieve this in SQL, but what I want to do, is give the user the freedom to choose these parameters! So in this case I would put on the report page three slicers: two date slicer (for beginning and end date) and one ID slicer. Once the user sets all three slicer to some value, than in the background the SQL stored procedure gets called and two result columns (date and data) are returned and visualised on the report (for example in a line chart).

 

Obviously I want to do more complex stuff with this ability... But the main point is that is it possible to pass the slicer parameters to an SQL stored procedured, and than visualise the result in some way in power BI? 

Highlighted
Community Support
Community Support

Re: Pass user input parameters from report page to SQL stored procedure

Hi @Astorre72

 

Yes, create query parameters then go to data source setting>select the parameter you need and put your exec queries in Sql statement to get your SQL stored procedure,see below pics:

 1.PNGAnnotation 2020-07-22 091705.png

Here is a reference about how to use query parameter.

https://docs.microsoft.com/en-us/power-query/power-query-query-parameters#:~:text=To%20create%20or%2...

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

View solution in original post

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors