Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Questions on adding sprocs that contain parameters.

Greetings. Using this awesome article, I was able to figure out how to add sprocs that accept parameters to my first report. However, I still have a couple questions:   The article states that thi...
Need Help
v-lid-msft's avatar
v-lid-msft
Community Support
6 years ago

Hi Anonymous ,

 

For first question, if you mean using directquery with Store procedure please refer to the post below that makes use of OPENROWSET : https://community.powerbi.com/t5/Desktop/Calling-SQL-stored-procedure-with-Direct-Query/td-p/224831

 

For second question, please try to invoke the new code/function, it will generate a query with table (Step 4 in the blog)

 

Sorry for that we can not understand the last question clearly , do you mean how to access the Store Procedure without using sql statement in advanced editor?


Best regards,

Anonymous's avatar
Anonymous
Not applicable
6 years ago

 

I'm attempting to rewrite # 1 w OPENQUERY, I can make it go as long as I'm not attempting to pass in a paramter:

 

This works:

let
Source = Sql.Database("myServer", "myDB", [Query="select * from openquery ([myServer], 'exec myDB.dbo.usp_rpt_ServersDatabases');"])
in
Source

 

This doesn't:

let
SQLSource = (dbInstance_name as text) =>

let
Source = Sql.Database("myServer", "myDB", [Query="select * from openquery ([myServer], 'exec myDB.dbo.usp_rpt_ServersDatabases '"& (dbInstance_name) &"'"])
in
Source
in
SQLSource

 

I've attempted several variations of this, and it always yells about one thing or another. Is this possible and I'm jsut doing it wrong, or is it not possible?

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Regaring # 2..

     

    I can in fact invoke a new function which I can then Publish to my workspace. However, I then am stuck with whatever data was captured when I fed in the paramter back in Power BI Desktop -- I cannot input a new paramter and get different data. 

     

    What am I missing here?