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 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!

Need Help

4 Replies

  • 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?