Build with an Azure free account. Get USD200 credit for 30 days and 12 months of free services.

Start free today

Pass parameter to SQL Queries statement using Power BI

Play Pass parameter to SQL Queries statement using Power BI

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"

  • User profile image
    Pierre Yves

    hi Charles

    Why didn't you try to create a calculation view in SAP HANA to use parameters ?
    I am just wondering if this could be helpful...I am starting to look at options for PBI sources and I am not really comfortable with so many "tricks" to extract and play with data...

    What do you think ?

  • User profile image
    Saurabh

    I am using Power BI report with direct query and parameter. It is working fine as expected but not after publishing the report to Power BI service the parameters are not visible in the PBI service.

    Is Power BI Direct Query with parameter not supported over Power BI service? Any documentation to support this or any plan when it will be available?

  • User profile image
    jeevith

    My question is,
    can we parameterized the date ,means you are giving 1996-07-04 like that rite..
    i want to do it dynamically ,that means the where clasue should get the data from date slicer in powerBI report ad then value should be assigned in where clause? can we do that

  • User profile image
    Florida

    Hello team:

    What can we do about the where clause when folks only have two of three parameters? Leaving the third parameter empty returns an error. Does anyone have an idea how to build the proper where clause when only two of three parameters are filled in?

    Thanks.

Add Your 2 Cents