Pass user input parameters from report page to SQL stored procedure

[Astorre72]

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!

[v-kelly-msft]

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!

[Astorre72]

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? 

[v-kelly-msft · Accepted solution]

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:

 

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!

[vanessafvg]

can you give more detail? freedom to do what?