Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
vijeth_sankethi
Frequent Visitor

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

 

10 REPLIES 10
pthapa
Helper V
Helper V

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
Solution Sage
Solution Sage

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. 

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
Not applicable

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

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

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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
Not applicable

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!

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
Not applicable

@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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.