cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
carlosDash
Advocate IV
Advocate IV

How do I pass parameters to my SQL statement?

How can I use parameters to create a dynamic SQL statement? I specified two parameters "DateStart" and "DateEnd" which I want to include in my Data source's SQL statement, but I don't know what the proper way to reference them is.  My aim is to have the users be able to adjust the parameter dates at the opening of the report.

1 ACCEPTED SOLUTION

I got it to work. Here is my code ( DateBegin & DateEnd parameters have been set to 'text'):

 

let
    StartDate=DateBegin,
    EndDate=DateEnd,
    Source = Sql.Database("this-is-the-database-url.com", "TableNameHere", 
    [Query="SELECT * FROM [TableNameHere].[dbo].[Logs] WHERE CreatedTime >='" &StartDate& "' AND CreatedTime <='" &EndDate& "' ", 
    CommandTimeout=#duration(0, 0, 10, 0), HierarchicalNavigation=true, MultiSubnetFailover=true])
in
    Source

Oddly enough, I got a bunch of error messages when exiting the 'Query Editor' (as show in my previous post) but when I created everything again from scratch, it works perfectly. 

 

I then saved the Power BI document as a template. When you open the template, you get prompted to enter the "DateBegin" and "DateEnd" parameters. Enter the parameters and the SQL query gets made, voila! Cat Happy

View solution in original post

19 REPLIES 19
meeninthala
Frequent Visitor

Hi,

 

I have a similar requirement. When we follow the same query style proposed, we are able to fetch the data succesfully, but I have the problem of data refresh in the Power BI Service.

 

The data set fails to refresh in Power BI service when the parameter is sent along with main query in the where clause. 

 

Can you please suggest how to overcome this problem.

 

Thanks,

Mohan Krishna Eeninthala

manjirit
Helper I
Helper I

All the tutorials and posts I saw were about how to pass the query parameter in the 'M' query in Power BI advance editor and nothing about how to actually pass a parameter by editing the souce query SQL statement. 

 

Any idea about that?

 

The main problem with adding the parameter in the Advanced Editor is that, once its edited there, then the setting wheel next the Source of the query disappears and there is no way to see or edit your SQL statement after that. 

Any more insights on this? Does Power BI support passing a parameter to the SQL query?

Greg_Deckler
Super User IV
Super User IV

So, you have Power Query parameters and you want to use them in a SQL statement?

 

If that is the case, see these articles:

 

https://reevessmith.wordpress.com/2014/08/19/power-query-and-stored-procedures-with-parameters/

 

https://blog.oraylis.de/2013/05/using-dynamic-parameter-values-in-power-query-queries/

 

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@Greg_Deckler@KHorseman I was actually able to do what I set out to do with the parameters, I just didn't know the correct syntax. I used the method described in “Passing Parameters To SQL Queries With Value.NativeQuery() In Power Query And Power BI” but just simply defined the variables as "DateBegin=DateBegin, DateEnd=DateEnd". It works great within the query editor but once I hit 'Close and apply', I get this error message:

 2017-01-26_13-16-43.png

I tried to find some solutions to overcome these but I wasn't able to find anything useful.  I don't really understand what the first error message means.

 

Trying to declare the @DateBegin variable with the SQL DECLARE and SET methods only resulted in an error message saying "@DateBegin has already been declared". 

 

Any ideas on how to get forward with these issues would be appreciated Smiley Happy

I got it to work. Here is my code ( DateBegin & DateEnd parameters have been set to 'text'):

 

let
    StartDate=DateBegin,
    EndDate=DateEnd,
    Source = Sql.Database("this-is-the-database-url.com", "TableNameHere", 
    [Query="SELECT * FROM [TableNameHere].[dbo].[Logs] WHERE CreatedTime >='" &StartDate& "' AND CreatedTime <='" &EndDate& "' ", 
    CommandTimeout=#duration(0, 0, 10, 0), HierarchicalNavigation=true, MultiSubnetFailover=true])
in
    Source

Oddly enough, I got a bunch of error messages when exiting the 'Query Editor' (as show in my previous post) but when I created everything again from scratch, it works perfectly. 

 

I then saved the Power BI document as a template. When you open the template, you get prompted to enter the "DateBegin" and "DateEnd" parameters. Enter the parameters and the SQL query gets made, voila! Cat Happy

View solution in original post

What editor in Power BI were you using to enter this code?

 

I want to do this for my MYSQL data and pass parameters through my query code. 

 

Please let me know.
Thanks.

I have tried passing parameters in SQL statment. It is working for me.

But when i publish it to Power bi service, where do i have to input the parameter value?

hey ,

 

where you define

CreatedTime >='" &StartDate& "'

 

This assuming the StartDate is text,

 

How would i do it for an integer in my below statement

Where [Store No]= '" &Branch& "'"

 

My Store Nos are all integer

i currently get error message :

Expression.Error: We cannot apply operator & to types Text and Number.

@peterhinton

Please convert data type using Number.ToText(param)

Here's an example I got working using the AdventureWorks DB.


I created a sproc that accepts one int parameter.  I created a parameter in PowerBI of type ANY called IDIN.  

 

Here's my query:

 

= Sql.Database(".\MSSQLSERVER2016", "AdventureWorksDW2014", [Query="Exec dbo.testfrompbi "&IDIN&"", HierarchicalNavigation=true])

 

 

It's been a long time since I last touched this.  I don't remember the syntax off the top of my head but did you try to mess around with the " " surrounding the parameter statement? Try removing them?

the column of data i was working with in my table had the numbers saved as 'FLOAT'  format, i was able to convert them to propper integers and it now worked, appreciate your reply though, Thank You,

Thanks for the reply. Both of these tutorials seem to use Excel and I can't quite wrap my head around on how to apply them to Power BI. Is there no easy way to reference the parameters I already have within Power BI? Like just somekind of a syntax for calling a variable in the SQL statement like "@DateEnd" or "{DateEnd}"? 

 

The only tutorial I've found so far on what seems to be what I want to do is this: Passing Parameters To SQL Queries With Value.NativeQuery() In Power Query And Power BI. However, in that example it doesn't make sense to me that the parameters get their values from the declaration within the Power Query statement and not from Power BI.

I think you're under something of a misaprehension about how data is retrieved in Power BI. The user viewing a published report does not have any way to affect the query behind the data in that report. That query simply is what it is, and it is either refreshed on a schedule in Import mode, or it is refreshed on demand if you're in DirectQuery. In any case there's no way to give the end user an input on the report that would affect the query. The dataset merely contains whatever the query has already returned. The user can be given slicers and other filtering tools on a report that will filter the results on the page, but that filtering happens within the data already in the report after the queries have completed.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




I've seen tutorials where the Power BI report is saved as a template and upon opening the template, the report asks to choose from a list of parameters (country for example). Are you telling me there is no way to implement SQL date queries in this manner? I'm currently working with a massive SQL table which has several gigabytes of data. Naturally, I can't download the entire thing and do the filtering within the view. I really need a way for the user to be able to define the SQL query one way or another. 

Anonymous
Not applicable

Hey did you get any work around for the same?

 

I have also been working on Huge Set of tables and need to filter them based on the bases of date.

 

Please let me know is there a way to implement the same.

 

Appreciate the Help!

 

 

Step one is creating your custom SQL statement. I like to add my placeholder for a parameter when I create the SQL statements and replace those with the parameter once defined. The parameters need to be concatenated to the query using & outside of the quotes holding the query. If you created a table with a single SQL query, your M may look like the following.

 

Non-Parameterized Query

let
Source = Sql.Database("MyTestServer", "MyTestDatabase", [Query="SELECT test_key from MyFactTestTable WHERE datekey >= 20190101 AND datekey <= 20190201"])
in
Source

 

Parameterized Query

let
Source = Sql.Database("MyTestServer", "MyTestDatabase", [Query="SELECT test_key from MyFactTestTable WHERE datekey >= " & StartDate & " AND datekey <= " & CloseDate"])
in
Source

Hello CarlosDash 

 

I am in similar situation like this , i have huge DB that i can not download all together so need user to be able to select parameters and then get data ccordingly , how did you approach it ? Can you please help me here .

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors