Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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/Desktop/Pass-Input-Parameters-to-SQL-Queries/m-p/193651#M85220
Can anyone please help me with this?
Thanks,
Vijeth
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
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
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
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
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
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
80 | |
65 | |
52 | |
50 |
User | Count |
---|---|
211 | |
87 | |
80 | |
69 | |
60 |