How do I pass parameters to my SQL statement?

[carlosDash]

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.

[Anonymous]

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 ? 

[meeninthala]

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]

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. 

[dataviznz]

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

[Greg_Deckler]

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/

[carlosDash]

@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:

 

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 

[carlosDash · 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! 

[Kemad]

Thank you so much !

I have been racking my brain half the day to make my StartDate and EndDate custom functions readable directly in the Source Query.

When I use a SQL statement directly in the Database lookup, I can write BETWEEN '2022-12-01' AND '2022-12-31', and it works perfectly! But this has to be done in the database connection directly.

Once i tried editting it directly in the Source Query in PowerQuery - nothing seemed to work. But deleting BETWEEN and rewriting to PowerQuery logic like yours using WITH AND instead - worked just fine now!

Thank you 😄

[richie231186]

I just have to say, THANK YOU GOOD SIR! your a life saver. serously spent 3 hours trying to solve this problem.. went through maybe 20 solutions and vidoes. And where I ended up was syntax errors because everyone says to type variables as &variable when it should be &variable&. This is actually for excel but the same applies. 

[vacuesta]

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.

[AishwariyaV]

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?

[peterhinton]

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.

[smfaizhaider]

@peterhinton

Please convert data type using Number.ToText(param)

[brandonmc]

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])

[carlosDash]

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]

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,

[carlosDash]

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]

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.

[carlosDash]

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]

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!