cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

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!!

10 REPLIES 10
Highlighted
Community Champion
Community Champion

Re: user parameter in dynamic query

Hi there,

 

Please see this blog post for more information.

 

https://blog.oraylis.de/2013/05/using-dynamic-parameter-values-in-power-query-queries/

 

Thanks & Regards,

Bhavesh

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
Highlighted
Community Champion
Community Champion

Re: user parameter in dynamic query

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

 

Capture.PNG

 

Highlighted
Regular Visitor

Re: user parameter in dynamic query

 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!

Highlighted
Regular Visitor

Re: user parameter in dynamic query

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!!

Highlighted
Microsoft
Microsoft

Re: user parameter in dynamic query

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."):

5.PNG

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

Highlighted
Regular Visitor

Re: user parameter in dynamic query

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!!

Highlighted
Microsoft
Microsoft

Re: user parameter in dynamic query

Hi raji,

 

Currently I don't think this could be done under Report level with users.

Query Parameter is available ehn query the data, not after the query. If we would like to filter the report, we could add the slicer Visual. Which user could be able to select in Reports:

slicers in Power BI

Regards

Highlighted
Resolver I
Resolver I

Re: user parameter in dynamic query

 

Here is an example using a HANA database , with working syntax

enjoy.  Note you must use the internal SAP hana column names , the column displayed in PBI  are "labels" and may have spaces in them or be completely different to the actual sap columns

 

 

let
xx = "0000610127",
sel = "select ""ColumnName"" from ""ZXXX.XXX::TABLE""  where ""ColumnName"" = '"  & xx & "'  ",
Source = SapHana.Database("Server:12345", [Query=sel])
in
Source

 

Highlighted
Super User IV
Super User IV

Re: user parameter in dynamic query

Hi @raji

 

Are you able to provide your Parameter Name, as well as a sample or the actual query?

 

I have done this in the past and it a process of making sure you have got your parameter names and Power Query (M) syntax correct.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors