Forum Discussion

carlosDash's avatar
carlosDash
Advocate V
9 years ago
Solved

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 p...
Need Help
  • carlosDash's avatar
    carlosDash
    9 years ago

    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! :cathappy:

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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 ?