cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vijeth_sankethi Frequent Visitor
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

 

9 REPLIES 9
Seward12533 New Contributor
New Contributor

Re: Execute stored procedure with user parameters

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. 

Anonymous
Not applicable

Re: Execute stored procedure with user parameters

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 Frequent Visitor
Frequent Visitor

Re: Execute stored procedure with user parameters

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

vijeth_sankethi Frequent Visitor
Frequent Visitor

Re: Execute stored procedure with user parameters

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

Anonymous
Not applicable

Re: Execute stored procedure with user parameters

@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

Community Support Team
Community Support Team

Re: Execute stored procedure with user parameters

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.
vijeth_sankethi Frequent Visitor
Frequent Visitor

Re: Execute stored procedure with user parameters

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

wsabuga Frequent Visitor
Frequent Visitor

Re: Execute stored procedure with user parameters

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!

Highlighted
vijeth_sankethi Frequent Visitor
Frequent Visitor

Re: Execute stored procedure with user parameters

Hi @wsabuga ,

 

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

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 279 members 2,740 guests
Please welcome our newest community members: