How to call SP from report UI ?

Hi bhaskar812 ,

What you are looking for is called Dynamic M Parameters for SQL Server https://powerbi.microsoft.com/en-us/blog/power-bi-february-2022-feature-summary/#post-18588-_Toc95419003

DebbieE has written a blog post about how to implement it with SQL Server and a predefined list to select the parameter values from: https://debbiesmspowerbiazureblog.home.blog/2022/03/17/power-bi-february-2022-updates-dynamic-m-query-parameters/ 

cwebb has written a blog post about how to implement it with SQL Server and no predefined parameter values, i.e. the user enters any parameter value in the filter or slicer: https://blog.crossjoin.co.uk/category/dynamic-m-parameters/ 

Be aware that these two examples use normal SELECT queries, not stored procedures. It works with stored procedures as well, but this has a few more challenges:

• The feature is no longer in preview, so if you still read that you need to enable this as a preview feature in Power BI Desktop, you won't find it: That's no longer necessary, it's general availability now.
• Your table that loads from the stored procedure must be initially created in direct query mode with a native SQL query. When being asked to confirm the security warning for the native query, you need to confirm.
• Since Power BI automatically wraps queries that are issued in direct query mode into a select * from ( your native query here ) in the background, your native query cannot just be EXEC spMyStoredProc %Parameter='Value' because there is no such thing like select * from ( EXEC spMyStoredProc %Parameter='Value' ) on SQL Server. Instead your native query must look like (and you must include the database and the schema in the EXEC statement as shown!):

SELECT * 
FROM OPENQUERY(
    [MyServer], 
	'SET FMTONLY OFF
	 EXEC [MyDatabase].[MySchema].[MyStoredProc]
         @Parameter = ''Value'''
    )

• This solution is only supported on SQL Server on prem, SQL Server on Azure VM, and Azure SQL Managed Instance, not on Azure SQL Database
• You might need to configure your SQL Server to support all required functionality:

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO
EXEC sp_serveroption 'MyServer', 'DATA ACCESS', TRUE
GO

• Your table containing the parameter values to select from in the filter can be in direct query mode (like in Debbie's example) or in import mode (like in Chris' example).
• The datatype used for the parameter values column in the table and the datatype of the PowerQuery parameter must match, otherwise you won't find the option to bind the paramter in Power BI.

Don't give up if it gets challenging! I've tried in Power BI Desktop and published to Power BI service and it works.

BR

Martin