Passing parameters to a stored procedure from the URL of a published report

[Anonymous]

I am currently trying to check the feasibility of re-creating SSRS reports as Power BI reports (not migrate as Paginated Power BI Reports). I have set up a report in the Import mode and used several stored procedures. All these work fine with default parameter values, as well as dynamic values within the Power BI Desktop. However, once I publish the report, I am unable to pass the parameter value to invoke the SP and display the data. I feel that this goes against the principle of Power BI, but I just wanted to know if there is a workaround. My ultimate aim is to embed the report in my application. The application captures the User ID of the logged in user, which inturn is used as a parameter for my stored procedure (along with several other parameters). 

Any help would be much appreciated.

[collinq]

Hi @Anonymous ,

Have you seen either of these articles?  They might be helpful:

Dynamic M query parameters in Power BI Desktop (preview) - Power BI | Microsoft Docs

Chris Webb's BI Blog: Using Power BI Dynamic M Parameters In DAX Queries Chris Webb's BI Blog (crossjoin.co.uk)

That said, if those are not helpful or get exactly what you need I was wondering about making slicers in Power BI match the Stored Procedure and then pass those slicers from the report to run the Stored Procedure?

[Anonymous]

Hi Collin,

Thank you for the resources. I've gone through them and could not find much help. The Microsoft documentation mentions that it is applicable only for M based data sources and not for SQL, so I'm not sure if I could use it as I'm working with a MS SQL database. 

On the topic of creating slicers to match the stored procedure logic, I'm afraid that it isn't feasible to my requirement as the stored procedures have several dynamic statements that run into 1000s of lines of code. I'm trying to work with close to 50 SSRS reports, totalling to over 150 such parameterized stored procedures which have been vetted over years. Hence, trying my best to re-use the code instead of recreating the logic within Power BI. 

Not being able to call stored procedures through the DirectQuery method and not being able to pass dynamic parameters to the stored procedures through the report URL have tied my hands.

Power BI Paginated Reports seem to be the only feasible solution for now, although it is currently a costly solution for very little value add.

Any suggestion/guidance on the approach is appreciated.

[collinq]

Hi @Anonymous ,

I understand that frustration!  As far as I can tell, you can't call the Stored Procedure with Direct Query.  I am wondering if you try the Power App community - somebody there may have an answer by using Automate/Power Flow and then temporarily storing the data or otherwise passing parameters to a Stored Procedure based on URL/userid/slicer information.