cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
carlosDash Regular Visitor
Regular Visitor

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

Accepted Solutions
carlosDash Regular Visitor
Regular Visitor

Re: How do I pass parameters to my SQL statement?

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

16 REPLIES 16
Super User
Super User

Re: How do I pass parameters to my SQL statement?

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/

 

 


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

carlosDash Regular Visitor
Regular Visitor

Re: How do I pass parameters to my SQL statement?

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.

KHorseman Super Contributor
Super Contributor

Re: How do I pass parameters to my SQL statement?

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? Please mark it as a solution.

Proud to be a Datanaut!
carlosDash Regular Visitor
Regular Visitor

Re: How do I pass parameters to my SQL statement?

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. 

carlosDash Regular Visitor
Regular Visitor

Re: How do I pass parameters to my SQL statement?

@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

carlosDash Regular Visitor
Regular Visitor

Re: How do I pass parameters to my SQL statement?

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

peterhinton Frequent Visitor
Frequent Visitor

Re: How do I pass parameters to my SQL statement?

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.

carlosDash Regular Visitor
Regular Visitor

Re: How do I pass parameters to my SQL statement?

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?

peterhinton Frequent Visitor
Frequent Visitor

Re: How do I pass parameters to my SQL statement?

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,

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 45 members 1,217 guests
Please welcome our newest community members: