I am creating an SSRS report in VS 2019 and have an ODBC datasource using the Cloudera ODBC Driver for Apache Hive and am unable to get parameters to work so far.
I have created a dataset, query type text and everything works fine if I have static values in my query. I need to create parameters and have tried this as it is supposedly how to indicate parameters when using ODBC:
select * from clients where client_id = ?
The issue is when I test the query or click 'ok' to close the query designer/dataset properties I receive this error message and the field set does not update (and report is considered invalid):
An error occurred while executing the query. ERROR [42000] [Cloudera][Hardy] (80) Syntax or semantic analysis error thrown in server while executing query. Error message from server: Error while compiling statement: FAILED: ParseException line 24:16 cannot recognize input near '?' in expression specification
I have tried "@", "?", using [] around those characters, using parenthesis, and several other variations I found on google. When I use the ? or @, SSRS pops-up the standard define query parameters dialog but no matter what I do there it does not seem to make any difference.
It is almost like SSRS (or ODBC) is not substituting the parameter placeholder and just passing it through which HIVE does not know what to do with it.
Any ideas?
I use parameters by passing into filters of the dataset but its not a optimized solution another workaround is wrap your query inside an expression
="select name from table_a where gender='" & parameter_name.value & "'"
define fields manually and view the report.
If you find any other solution kindly correct this