How to pass a Report Builder parameter to Athena via text query?

[jayrey48]

Hi all - 

I'm developing a paginated report in Report Builder with Athena as a data source via ODBC. The final report is published to Power BI service with an On-Prem Gateway, which is working fine.

What I cannot figure out is how to pass a parameter value back to Athena.

Typical piece of query code using ? to trigger a parameter in Athena: 

Select

thing1

,thing2

,thing3

from some_table

where thing1 = 'MR'
and thing2 = 'C4'
and thing3 = ?

I have proved that the Report Builder parameter is picking up the expected value, and I can hard code it to have a single quote on each side so can be seen as a string. But in the query, nothing works to replace the ? placeholder. I have tried @Parameter, :Parameter, wrapping value in all manner of single and double quotes. Or, if the query syntax gets validated, I get a "Fractional data truncated while performing conversion" error, which leads me to believe the value isn't being seen as a string. Is this just something that cannot be done? 

(note: I can pass the parameter values in a filter, but it took 15 minutes to return the report in Power BI Service because it had to fetch ALL 1.5 million + rows before it applied the filter... I'd really like to achieve the faster results I know would happen with a parameter applied before the fetch.) 

[jayrey48]

I am writing a query in Report Builder that gets passed to Athena. 

Athena parameter queries take the form of ? and do NOT accept any of the Report Builder syntax (@Parameter, :Parameter, etc.) in a direct text query. 

I can filter on a parameter value in the Data Set properties, but that is applied after the query returns and with a big data set, it takes a long time and sometimes times out. 

I do appreciate you responding, but your responses lead me to believe that you do not have any experience working with Report Builder and Athena.  My initial suspicion was that passing a parameter in a query to Athena is not possible at this time. Is that a fair assumption? 

[jayrey48]

Thanks for the response. 

I have already verified all of the above. Athena will not accept the "@parameter" or "in (@parameter)" syntax - it has to be the ? in the query in Power BI Report Builder. And a multi-value parameter is not allowed by Athena.

This is the error message I get when trying the "thing3 = @Parameter" construct:

ERROR [HY000] [AmazonAthena][AthenaClientError]: ExceptionName: InvalidRequestException, ErrorType: 130, ExceptionMessage: line 74:12: mismatched input '='. Expecting: <predicate>

Please advise whether this is something that the application does not allow and how I can submit a feature request if not. Again, this works as a filter but not a query parameter.

[v-yiruan-msft]

Hi @jayrey48 ,

I'm a little confused. Did you apply the query with the parameter in Athena or Report builder? The previous reply is base on the report builder, and the "?" can't be used as paremater name...

Best Regards

[v-yiruan-msft]

Hi @jayrey48 ,

It sounds like you're encountering a common issue when working with parameters in Report Builder and Athena. Please follow the steps below to do it:

1. Make sure create the parameter correctly. You can refer the following official documentations to get it:

Create parameters for paginated reports in Power BI Report Builder - Power BI | Microsoft Learn

Add a multi-value parameter to a paginated report (Allow multi values)

2. Check and validate that the data type of parameter and the field which will apply the parameter are same

3. Modify the query as below:

Single selection:

Select 
thing1
,thing2
,thing3
from some_table
where thing1 = 'MR'
and thing2 = 'C4'
and thing3 = @parameter1

Allow Multiple selections:

Select 
thing1
,thing2
,thing3
from some_table
where thing1 = 'MR'
and thing2 = 'C4'
and thing3 in (@parameter1)

Best Regards