Forum Discussion

raji's avatar
raji
Regular Visitor
9 years ago

user parameter in dynamic query

Hello!

 

I'm trying to pass the value of a parameter to my dynamic query statement so that I can limit the dataset on the source db (dealing with extremely large data).

 

I tried the following... but it doesn't transfer the value of parameter StartDate to variable p_start_date.  How can I get the parameter value passed so I can use it in my dynamic query?

 

 let
p_start_date = fnGetParameter("StartDate"),
Source = SapHana.Database("server",
[Query="select ""VBAK"".""VBELN"", ""VBAK"".""AUART"" #(lf)from ""VBAK"" #(lf)where ""VBAK"".""ERDAT"" > '& p_start_date &'"])
in
Source

 

 

Many thanks!!

Need Help

10 Replies

  • ankitpatira's avatar
    ankitpatira
    Community Champion
    9 years ago

    raji I think you're just missing double quotes. Pass in parameter as shown in image below with double quotes.

     

     

    • raji's avatar
      raji
      Regular Visitor
      9 years ago

       Thanks ankitpatira !  I tried that but got an error:

       

      New script:

      let
      Source = SapHana.Database("server",
      [Query="select ""VBAK"".""VBELN"", ""VBAK"".""AUART""
      from ""schema"".""VBAK""
      where ""VBAK"".""ERDAT"" > """& Start & """
      "])
      in
      Source

       

      It shows the following query:

      select "VBAK"."VBELN", "VBAK"."AUART"
      from "schema"."VBAK"
      where "VBAK"."ERDAT" > "20160101"

       

      but the hana system needs the date without the quotes as it's throwing the following error message:

      DataSource.Error: ODBC: ERROR [S1000] [SAP AG][LIBODBCHDB DLL][HDBODBC] General error;260 invalid column name: 20160101: line 3 col 24 (at pos 200)
      Details:
      DataSourceKind=SapHana
      DataSourcePath=server
      OdbcErrors=Table

       

      Any ideas? Thanks!

  • raji's avatar
    raji
    Regular Visitor
    9 years ago

    I think I got the syntax, but now I get the following error:

     

    "Mixing DirectQuery and imported data is not supported."

     

    Does that mean a parameter cannot be used for user input and passed to a dynamic query?

     

    Or does anyone know how to get around this?

     

    Many thanks!!

    • v-micsh-msft's avatar
      v-micsh-msft
      Microsoft Employee
      9 years ago

      Hi raji,

      Parameter is supported under Direct Query. Check:

      Deep Dive into Query Parameters and Power BI Templates

      This error “Mixing DirectQuery and imported data is not supported." Might be caused by the Parameter status, please right click the parameter, and uncheck the Enable Load option (If checked, after click close and apply, Power BI desktop would generate the error "Mixing DirectQuery and imported data is not supported."):

      In addition, please take a notice of the data type of the data source and the parameter, make sure the type matches.

      Please reply back if you need any further assistance on this.

      Regards

      • raji's avatar
        raji
        Regular Visitor
        9 years ago

        Sorry for the delay - I was on business travel and wasn't able to check this... back now...

         

        Looked at the link and I think I see the issue/disconnect now... I was looking for a query parameter that the user could enter and I could pass to the select statement to limit the rows returned (vs a slicer and filtering on it since the dataset is millions of records).  However, using the link to query params and templates provided above, it looks like this is only possible using Desktop BI vs published reports that users are using.

         

        If I misunderstood and there is a way to do this where the users can enter the parameter value from the report, would love to hear!

         

        Thanks all for your feedback!!