Dynamic SQL queries as parameter

Question

I need a Report where a user has to choose 2 parameters. The first parameter contains the years (2017, 2016...), and the second one contains the ID process. Depending on the process that the user chooses, the SQL statement will be one or another. The parameter year is part of the WHERE clause of the SQL contained in the second parameter.

So I have this report with 2 parameters (param_year, Indicador). Query parameter is done using a table datasource, where the IDs column contains the SQL sentences and the Values column contains the text the user must select.

So what I'm doing next is to set ${Indicador} as the SQL statement in the JDBC connection that I have done to the Database. This is reporting me an SQL error

"Failed at query: ${Indicador}.

Any suggestions will be appreciated. Thanks in advance.

Answer 1

Another option is to create multiple datasources in your Master/sub report, then select appropriate datasource using PRD expression on Master/sub Report -> Attributes -> query -> name attribute.

More detailed explanation:

1. Create a query (I mean a query as a PRD object, which uses the PRD datasource) for every SQL string you need and move the SQL strings from the parameter table into Report Designer queries definitions.
2. Replace the SQL strings in your parameter table with names of corresponding queries, e.g:
3. Use the value of your parameter (which should be equal to the PRD query name) as value for Master/sub Report -> Attributes -> query -> name attribute: