Pass parameter to SQL Queries statement using Power BI

Sign in to queue

The Discussion

  • User profile image
    PLMASIE

    That's SQL Injection, isn't it? Btw it'd be nice to allow report user to modify those parameters via on-report visual. Is it possible? How to do it? This video actually shows a string concatenation and nothing more.

  • User profile image
    chanmm

    @PLMASIE: Maybe you can have a video to show me how a SQL Injection can attack Power BI.

  • User profile image
    PBI Guy

    Thanks for the tutorial, it is very helpful!

  • User profile image
    PinkPanther

    This is a clear video but it seems (from this video and others) that you always have to load all the data before you can specify parameters in Power BI. I have an sql query that uses a date parameter in the where clause. If I modify the where clause to specify: where x.date >= 01/01/2000, it would return well over 10,000 rows of data. What I want to be able to do instead is specify in the where clause: where x.date = @date and then specify a parameter in Power BI that the report user can complete to see data from 01/01/2000 or 01/02/2000 or 01/03/2000 etc. Is this possible?

  • User profile image
    chanmm

    Do you mean you want the date between 01/01/2000 and 01/02/2000?

  • User profile image
    PinkPanther

    No, the date has to be from 01/01/2000 onwards, no cut off. Therefore, I want the user to be able to enter for example 01/09/2013 and be able to see the data. It's possible to do this in excel with an sql query connection but I don't think this seems to be possible in Power BI.

  • User profile image
    chanmm

    Remove the AND condition just use single parameter should work. Like

    Source = Sql.Database(".", "northwnd", [Query="SELECT * #(lf)FROM Orders #(lf)WHERE OrderDate > '" & StartDate & "'"])

    Replace the parameter StartDate value to 2013-09-01 for example.

  • User profile image
    PinkPanther

    I appreciate your patience on this. I don't think I am being clear.

    I have an sql statement which starts with:

    Declare @date datetime = '01/01/2000'

    and then it ends with a where clause....

    WHERE b.date = @date

    I want the user to be able to enter any date into the report from 01/01/2000 onwards. I don't want them to have to edit the query.

    Is this currently possible in Power BI?


  • User profile image
    PinkPanther

    I should add that with the @ variables, the query takes 12 seconds to run in SSMS. If I take them out, after 5 minutes the query is still running. If I can't use the @ variables then I can't put this query into Power BI as far as I can see...

  • User profile image
    Rodrigo​Campos

    Hi @chanmm,

    How can I have 2 input boxes  in my power bi report where the user can introduce the StartDate and the EndDate?

    I'm used to SSRS where I can have at the top of my reports input boxes where the user can specify several parameters. Power Bi have anything like this?

    Thank you.

     

     

     

  • User profile image
    chanmm

    Hi Rodrigo​Campos

    Good question to me and this should be answered by the power bi team.

    The parameter will pop up if you save the pbix to pbit. Somehow this is not a solution to you I believe.

    For me, I will extract the data to another table using ETL. Then point the Power Bi report to that table. This is the workaround that I will use.

    Cheers

     

Add Your 2 Cents