Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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

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.

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.

 

Anonymous
Not applicable

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

Anonymous
Not applicable

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'.

Anonymous
Not applicable

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

Fergal

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors
Top Kudoed Authors