cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
radz
Frequent Visitor

Pass Parameters from a SP in SQL into parameters created in Power BI(which can accept values)

In short, "Pass parameters within a stored procedure(In SQL say) so that the parameter values can be called/used in parameters made in Power BI(query) ?"

 

For eg: I have a SP in SQL which has 2 parameters (para1 and para2) which can take multiple/single value(s).

I call this SP in Power BI Query Editor and I can now see it in my data sets. This can be done.

 

What I want is, to create a parameter (say para) in Power BI which has its source as the SP in SQL such that if I pass a value into para (in Power BI), then data loaded from the SQL Server (my data source) into Power BI corresponds only to the value(s) I added.

Is this possible?

 

Thanks in advance!

 

[This may help in getting required data loaded only into Power BI, saving time.]

2 ACCEPTED SOLUTIONS
ankitpatira
Community Champion
Community Champion

@radz if i understood you correctly,

 

you need to create a query first to sql server that executes a sp with hard coded value for parameter. then once data loaded in power bi right click that query and click create a function and craeate. Once function is created you need to right click -> Advanced Editor and replace code to parameterise hard coded values that will be supplied to sp parameter. hope that make sense.

View solution in original post

radz
Frequent Visitor

Hi @ankitpitara

Yes it made sense and helped me. Thanks.

But instead of creating a function, I directly replaced code in the Query created by going to Advanced Editor, to parameterize the hard coded values.

 

This was done by adding Power Bi Parameters by concatenating them using &.

Thanks for your help.

 

View solution in original post

8 REPLIES 8
ankitpatira
Community Champion
Community Champion

@radz if i understood you correctly,

 

you need to create a query first to sql server that executes a sp with hard coded value for parameter. then once data loaded in power bi right click that query and click create a function and craeate. Once function is created you need to right click -> Advanced Editor and replace code to parameterise hard coded values that will be supplied to sp parameter. hope that make sense.

View solution in original post

radz
Frequent Visitor

Hi @ankitpitara

Yes it made sense and helped me. Thanks.

But instead of creating a function, I directly replaced code in the Query created by going to Advanced Editor, to parameterize the hard coded values.

 

This was done by adding Power Bi Parameters by concatenating them using &.

Thanks for your help.

 

View solution in original post

hi guys - can yo show an actual example of the code you used here? I'd really appreciate it - thanks!

Hi FergaIK

 

example could be 

 

let
Source = Sql.Database("x", "x", [Query="Select distinct .Dates D where d.DatesAgo Between 0 and "&ToDate&" "])
in Source

 

 

best,

Mads

now that I think of it - maybe that won't work, as that's just a parameter in a SELECT statement, what I need is how to code the paramater into an OPENQUERY parameterised stored proc.

Here's the connection string as an example of what I'm trying to do:

 

= Sql.Database("myserver", "mydatabase", [Query="SELECT * FROM OPENQUERY ([myserver, 'EXEC mystoredproc @param1= ??? ')#(lf)", CreateNavigationProperties=false]

 

Any help/advise is much appreciated!

Fergal

 

Dear All ,

 

i can able to send parameter to sql server by entering value in text box (By edit parameters tab)

 

Can i get dropdown with auto populate values from database to choose values

@BalaVenuGopal, you can achieve this by options available in 'Manage Parameter' , in 'Suggested Values' you can provide 'List of values' or get values using a 'Query'.

Thanks for the info Mads, I will try that out and let you know how it goes

Fergal

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.