Forum Discussion

Astorre72's avatar
Astorre72
Frequent Visitor
6 years ago
Solved

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 lik...
Need Help
v-kelly-msft's avatar
v-kelly-msft
Community Support
6 years ago

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's avatar
Astorre72
Frequent Visitor
6 years ago

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?