CODEWITHSUNDEEP
sqlpentahowhere-clausepentaho-report-designer
Mascu
Mascu

Reputation: 33

Dynamic SQL queries as parameter

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.

Setting the parameter

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}.

Setting the parameter as the SQL statement

Any suggestions will be appreciated. Thanks in advance.

Upvotes: 1

Views: 1482

Answers (3)

Andrei Luksha
Andrei Luksha

Reputation: 1030

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: parameter table
  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: query name expression

Upvotes: 1

Cyrus
Cyrus

Reputation: 2195

If the table structure (output columns) for both queries is the same, you could put them together into one big SQL statement with UNION ALL and put in each query "WHERE ${Indicador} = ValueToRunThisQuery".

The optimizer should be smart enough to know the not-selected subquery is going to return zero rows and not even run it. You can supply a few null columns if one query has fewer columns, but the data types have to be the same for filled columns.

If the output table structure is different between the two queries they should be in different data sources, or even reports.

SELECT ID, BLA, BLA, BLA, ONLY_IN_A
FROM TABLE A
WHERE ${Indicador} = "S010"
UNION ALL
SELECT ID, BLA, BLA, BLA, NULL
FROM TABLE B
WHERE ${Indicador} = "S020"

Upvotes: 0

bhericher
bhericher

Reputation: 667

You need Pentaho Data Integration to do this kind of dynamic query

Upvotes: 0

Related Questions