Pass parameter to SQL Queries statement using Power BI

Play Pass parameter to SQL Queries statement using Power BI
Sign in to queue

Description

This video shows you how to pass parameters to SQL Queries statement using Power BI. It is not straightforward to pass parameters to the SQL Queries statement in Power BI Desktop. Enable the parameters passing on the SQL Queries statement then the SQL Queries will be Dynamic instead of Static.

The video is for any developers especially for Power BI developers or Excel Dashboard creators.

This is level 300

Embed

Download

Download this episode

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

     

  • User profile image
    Comaru

    Muito obrigado por disponibilizar este conteúdo, eu precisava muito deste material.

  • User profile image
    Charles Philpott

    I had a similar situation hitting very large SAP table Data sources (KONV - Condition Types, and OICQ7 Condition Type Formulas. KONV is in excess of 50 million records so dynamically filtering a narrow date range on the SAP side was highly preferred performance wise. I built a parameter table in Excel with a Begin and End date. To facilitate the need of using multi-quotation marks in the query syntax, I also added a (") Quote symbol to my Excel parameter table:
    Parameter Value
    Begin_Date '20190301'
    End_Date '20190331'
    Quote "

    I segmented the query into a series of build statements and then combined them to form the SQL Query that would hit SAP HANA. Once the results were returned, I continued just using the regular power qroup by function to further consolidate the results:

    let
    Build0 = Text.From(fnGetParameter("Quote")),
    Build1 = "Select ""Condition"", ""Item_No"", ""FKDAT"", ""IV01_Formula_Description"", ""ZP01_Formula_Description"", ""ZP03_Formula_Description"", ""ZZP2_Formula_Description"", ""ZZQE_Formula_Description""#(lf)#(lf)FROM ""_SYS_BIC"".""Master_Data/KONV_CONDITIONS_W_OICQ7_FORMULAS"" #(lf)GROUP BY ""Condition"", ""Item_No"", ""FKDAT"", ""IV01_Formula_Description"", ""ZP01_Formula_Description"", ""ZP03_Formula_Description"", ""ZZP2_Formula_Description"", ""ZZQE_Formula_Description""",
    Build2 = " Having ",
    Build3 = Build0&"FKDAT"&Build0,
    Build4 = " >= ",
    BeginDate = Text.From(fnGetParameter("Begin_Date")),
    Build5 = " and ",
    Build6 = Build0&"FKDAT"&Build0,
    Build7 = " <= ",
    EndDate = Text.From(fnGetParameter("End_Date")),
    Dbquery1 = Build1&Build2&Build3&Build4&BeginDate&Build5&Build6&Build7&EndDate,

    Query1 = SapHana.Database("atxhanaprd:30015", [Query=Dbquery1]),
    #"Grouped Rows" = Table.Group(Query1, {"Condition", "Item_No"}, {{"IV01_Formula_Description", each List.Max([IV01_Formula_Description]), type text}, {"ZP01_Formula_Description", each List.Max([ZP01_Formula_Description]), type text}, {"ZP03_Formula_Description", each List.Max([ZP03_Formula_Description]), type text}, {"ZZP2_Formula_Description", each List.Max([ZZP2_Formula_Description]), type text}, {"ZZQE_Formula_Description", each List.Max([ZZQE_Formula_Description]), type text}})
    in
    #"Grouped Rows"

Add Your 2 Cents