Multiple SQL queries in a report

Question

The requirement is to generate a single report connecting to a single DB:

1. Query1 is a group by query and has a bar chart and pie chart based on it.
2. Query2 is a simple query on which a table gets created.

Both these queries need results based on a WHERE clause, which is supplied dynamically.

Can somebody point me to some examples on how to achieve this?

Thank you.

Answer 1

You can tell JasperReports to use a parameter to define part of the query using the $P!{PARAMETER_NAME} syntax. This tells JasperReports to use the literal value of PARAMETER_NAME as part of the query. You can then do:

1. Create a parameter named WHERE_CLAUSE in the report.
2. Give WHERE_CLAUSE a default value of 1=1.
3. Consider the following SQL statement:

   SELECT * FROM table WHERE $P!{WHERE_CLAUSE}

The $P! expression changes the literal SQL statement to:

SELECT * FROM table WHERE 1=1

That is a valid query. Note the difference between $P{} and $P!{} -- the exclamation mark (!) is important.

You can then supply the SQL conditions dynamically.