Passing Parameters to Power BI through Stored Procedure

RE: Passing Parameters to Power BI through Stored Procedure

We use Power BI in combination with SQL Server. For many of our tables, we maintain system versioning, which allows us to "travel back in time." The point in time to travel is selected by the user, leaving us with a few options:Import History Tables into Power BI:This approach places significant pressure on infrastructure due to the large size of the tables and results in a poor user experience. Additionally, we lose near real-time data freshness and nice sql syntax "FOR SYSTEM_TIME AS OF ".Use M Parameters with SQL Query Manipulation:This setup involves manipulating SQL queries as strings. While it can be done, it is super obscure way that makes sense only for very simple "SLECT * FROM " type of queries. Something a little more complex will be very difficult to write, read, change, and test.Fallback to SSRS Reports:Currently, this is our chosen solution.The same logic applies to any situation where a large amount of data for fetching could be significantly reduced by applying user selection.We aim to phase out SSRS and fully adopt Power BI. However, Power BI still lacks features to effectively supersede SSRS in scenarios like these. Is there a plan to address this limitation and improve the situation?

RE: Passing Parameters to Power BI through Stored Procedure

I can already find posts from 2017 on the web. All questions about this feature. Namely for "POWER BI Desktop".All this is already possible in the paginated report (Report Builder).And the report builder is ancient.It's already the year 2023 and there hasn't even been a statement from Microsoft yet.Maybe we should switch to Tableau Desktop after all?Because the license we pay you for the reporting service is not small.What I find annoying is that I now have no solution as to how we can migrate to the cloud.At the moment it's time to keep looking and hope that I'll find a solution soon. Hardcore would be if I develop the shi... with Access report generator.

RE: Passing Parameters to Power BI through Stored Procedure

RE: Passing Parameters to Power BI through Stored Procedure

It's absolutely ridiculous this feature isn't included yet. I work in an enterprise environment that has used parameterized stored procedures in SSRS reports for more than a decade. Recently we were asked to convert SSRS reports over to Power BI, which should have been a simple process. However, the lack of SP and parameter support has left us trying to "hack" a solution so our (very, very basic) reports can be converted. I'm going to be suggesting we use something else instead of Power BI, or that we just go back to SSRS. And this is obviously a feature that has been requested since Power BI was launched. Why in the world would such an obvious and expected feature not be included??? I would never have imagined MS would release a reporting tool that is missing the most basic functionality. For now, Power BI is useless for us unless we want to go back and completely rewrite every single one of our reports to no longer use Stored Procedures and parameters, which is just not going to happen.

RE: Passing Parameters to Power BI through Stored Procedure

I echo the better stated comments already posted. The underlying data behind a slicer selection should be able to be passed as a parameter to a SQL SPROC call.

RE: Passing Parameters to Power BI through Stored Procedure

Calling stored procedures, with and without parameters, is definitely needed as much of the heavy lifting can be done using SQL Server or other databases to take advantage of what they do best. This ability also needs to include the ability to call AWS RedShift stored procedures, with and without parameters. Currently RedShift requires two (2) SQL statements, one to execute the procedure and one to get the results. Power BI Desktop and Report Builder need to handle those two (2) statements. Even if the statements need to be separated by a semicolon or other character.

RE: Passing Parameters to Power BI through Stored Procedure

Execute stored procedure parameters

RE: Passing Parameters to Power BI through Stored Procedure

Yes it would be great idea to be able to pass a parameter exactly as you can in SSRS - sadly missing in Power BI
Same idea as https://ideas.powerbi.com/ideas/idea/?ideaid=9fce0220-d891-46cf-bcbe-1196466bdec8 - shame these cant be merged?

RE: Passing Parameters to Power BI through Stored Procedure

Recently I had a requirement to do some analysis but need to pass parameters to a stored procedure in SQL server ,I can not believe Power BI does not have this functionality already. We should be able to pass parameters from slicers to queries. Add this as soon as possible. Will have to go back to SSRS to do this for now.

RE: Passing Parameters to Power BI through Stored Procedure

This is needed for the on- premises version: PBIRS.
Either via a direct query, or if it is an Import, than with the ability to have a button on the report that immediately refreshes the data.

PBI already supports calling table based functions ( select * from ) , so this might be the shortest path, and since PBI supports the hidden parameters using Import, this could work. You just need to allow the parameters to be exposed at the run-time level, referenced by the function and then add a Button that forces an Import report to refresh. The exposed button is really just calling the OnDemand refresh, so this should not be much of an issue.