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.

      • carlosDash's avatar
        carlosDash
        Advocate V
        9 years ago

        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's avatar
    carlosDash
    Advocate V
    9 years ago

    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:

    • carlosDash's avatar
      carlosDash
      Advocate V
      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:

      • peterhinton's avatar
        peterhinton
        Frequent Visitor
        9 years ago

        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.