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:

carlosDash's avatar
carlosDash
Advocate V
9 years ago

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.

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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.