Text Parameter for Stored Procedure

[JJenkins2005]

Hello,

I've been asked to create a Power BI report using a couple stored procedures accessing some confidential data. I've played around with the parameter feature on PBIDesktop, but never attempting to pass the data to a stored procedure.  

Here is the proc with the dataset: 

EXEC dbo.usp__SSRS__GetLoginDetails @p_LocationCode = 'ORA';

The options for the Parameters are received using the following proc: 

EXEC dbo.usp__SSRS__GetLocations

ORA in the first proc is one of the location codes that are available from the second proc... 

I'm looking for a way to be able to a location code on the report, and have that passed to the stored proc to provide the data for the report.  

Thanks in advance for your assistance. 

- Jarrin 

[v-shex-msft]

HI @JJenkins2005,

Please refer to below link mentioned about invoke SP with query parameter.

Call an SP

let 
    SQLSource = (para as text) => 
let
    Source = Sql.Database("testserver", "testdb", [Query="EXEC dbo.usp__SSRS__GetLoginDetails @p_LocationCode =' "&para&" ' "])
in
    Source
in
    SQLSource 

Regards,

Xiaoxin Sheng

[JJenkins2005]

Thanks for the information. I was able to get that to work, but that appears to require the user to edit the query... Is there any way to run an SP, provide the user with a filter to select the parm they want, and once selected that is passed to the SP and the data is provided?  Users won't be able to edit the queries when we publish this to the service or PBIS.  

[v-shex-msft]

HI @JJenkins2005,

Unfortunately, current query parameter and custom functions not work on power bi service.

Regards,

Xiaoxin Sheng