Forum Discussion

vijeth_sankethi's avatar
vijeth_sankethi
Frequent Visitor
8 years ago

Execute stored procedure with user parameters

Hi All, 

 

I am still trying to learn the nuances of Power BI as I am from a SSRS background. 

 

I am trying to achieve the following in Power BI. 

The user should have an option to select a date as a Parameter. This date should be passed to the Stored procedure which after execution gives out a set of records and it will be shown on Report screen. However, I am not able to achieve this. 

 

I have tried various options and almost all of them deal with creating new parameters in Power BI desktop and changing it to get different rows in the model but we do not have any options to execute a Stored Procedure based on User's parameters selection. I have already gone through the below mentioned URLS and they didn't do much to help me. 

 

https://community.powerbi.com/t5/Integrations-with-Files-and/parameters-to-SP-or-Sql-query/td-p/57734

https://community.powerbi.com/t5/Desktop/Passing-Date-Parameters-to-a-Stored-Procedure/m-p/401610#M183575

https://community.powerbi.com/t5/Desktop/Pass-Input-Parameters-to-SQL-Queries/m-p/193651#M85220

 

Can anyone please help me with this?

 

Thanks,

Vijeth

 

Need Help

10 Replies

  • Seward12533's avatar
    Seward12533
    Solution Sage
    8 years ago

    Why not bring in all the data associate with a Data Table and then just use filters to control what is displayed?  I use PowerBI with hundreds of thousdans of records. 

      • vijeth_sankethi's avatar
        vijeth_sankethi
        Frequent Visitor
        8 years ago

        Hi Anonymous,

         

        This is the closest to the solution I have been searching for. Thank you..

         

        But Instead of the Drill down player, Can we use something like a Date picker / Text box which is used by the user and based on his selection, that date value would be passed to a Stored Procedure and results are shown in a table? 

         

        Apologies if the question is basic but I can't seem to get it working. 

         

        Thanks,

        Vijeth

    • vijeth_sankethi's avatar
      vijeth_sankethi
      Frequent Visitor
      8 years ago

      Hi Seward12533,

       

      I can bring all data into the datatable but it is not static. Based on the selected date, the calculations will change. So I can't bring all the data into the datatable in one shot. 

       

      Another way is to consider all the dates for the past 2 years, and calculate for each date and insert into a table and bring that table's contents over to the datatable. The amount of data would be huge. 

       

      I personally believe that this is not an apt solution. But if nothing works, i will do this itself. 

       

      Thanks,

      Vijeth

  • pthapa's avatar
    pthapa
    Post Patron
    2 years ago

    I had similar issues on my SSRS reports that I am migrating to PBI. Becasue pretty much all the stored procedures have the parameters on them which makes it hareder for us to run the stored procedure in PBI with parameters. 

    My suggestion to all is try to rewrite the stored procedure without the parameter, that would work better in this scenario. Parameters are meant to work as a filter in SSRS to retrieve the data from the database, however in power bi slicer will do the job. But, if you need to limit the data extract in PBI, apply some filter on date field in Stored Procedure at the first approach, that would help.

    Thanks,

    Pthapa