Execute stored procedure with user parameters

[vijeth_sankethi]

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/5773...

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

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

[pthapa]

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

[Seward12533]

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]

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

[Anonymous]

Hey @vijeth_sankethi

If you are using DirectQuery, you can achieve this by running SQL code in an R visual. It's quite the workaround but works great. Check out my video here if you're interested:

https://www.youtube.com/watch?v=3QiTBXfxzHA

Hope this helps,

Parker

[vijeth_sankethi]

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

[v-piga-msft]

Hi  @vijeth_sankethi,

Have you solved your problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please feel free to ask.

Best Regards,

Cherry

[vijeth_sankethi]

Hi @v-piga-msft, 

Unfortunately, I was not able to get the solution in the way I wanted. I had tried @Anonymous solution but it didn't work the way I expected. It is maybe because I am a newbie in Power BI and couldn't get it to work. I can give more details if you need.  So I didn't mark it as a solution. 

Thanks, 

Vijeth

[Anonymous]

Hi @vijeth_sankethi ,

I am wondering if there has already been a solution for this? I am also facing the same problem as I am moving all my SSRS reports to Power BI. I am also a newbie at PowerBI so a solution would be greatly appreciated. Thanks!

[vijeth_sankethi]

Hi @Anonymous ,

Unfortunately, I wasn't able to get that working in the way I expected. So Instead, I executed the Stored Procedure for all possible Input values and stored the results in a table. This table was then used to populate the Power BI report. Sorry for not being to help you. 

P.S. I wrote another job which calls the SP to populate the base table. This job runs daily and since my source is a Data warehouse which is populated daily, it worked out in the end. 

Thanks,

Vijeth

[Anonymous]

@vijeth_sankethi

Yep! Any selection you make on any slicer or visual will re-run the r script and execute the stored procedure. I just used the drill-down player to make it automated