Forum Discussion
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.
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 SourceOddly 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! :cathappy:
22 Replies
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/
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.
Greg_DecklerAnonymous 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:
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 :smileyhappy:
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 SourceOddly 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! :cathappy:
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?
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
Hello All ,
I have similar situation ,
I have created to 2 parameters RangeStart and RangeEnd. ( Both DateTime ) ..
Catch here is i have to run my query on SQL Server but it is using OpenQuery to get data from Oracle . So i need to Pass Dates to that ..
in the Advance Editor i can see , that query is getting values right in the required format. I can see that through the Native Query. All snapshots given below.
When i upload this in the Power BI Services it failes with Error.
{"error":
{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error":
{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","parameters":{},"details":[
{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},
{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"We cannot convert a value of type Record to type Text."}},
{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},
{"code":"Microsoft.Data.Mashup.ValueError.Reason","detail"{"type":1,"value":"Expression.Error"}},
{"code":"Microsoft.Data.Mashup.ValueError.Value","detail":{"type":1,"value":"[Format = \"yyyy-MM-dd\"]"}}],"exceptionCulprit":1}}}can anyone help ?
