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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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.

 

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors