Forum Discussion

JJenkins2005's avatar
JJenkins2005
Frequent Visitor
8 years ago

Text Parameter for Stored Procedure

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 

 

 

 

 

 

 

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      JJenkins2005
      Frequent Visitor
      8 years ago

      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.  

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        HI JJenkins2005,

         

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

         

        Regards,

        Xiaoxin Sheng