CODEWITHSUNDEEP
jasper-reportsireport
BoCode
BoCode

Reputation: 925

Multiple SQL queries in a report

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.

Upvotes: 3

Views: 6784

Answers (1)

Dave Jarvis
Dave Jarvis

Reputation: 31171

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.

Upvotes: 3

Related Questions