BIRT Reports, passing SQL to a query's parameter

In Crystal Reports you can pass SQL directly to a report’s parameter as a string and the SQL is resolved when the report is run.

For example, say you want to schedule a report to run at the beginning of each month for data pertaining to the previous month. The query for a particular report may look like this:

SELECT
  sum(jobid) jobs
FROM
  job
WHERE
  created_date >= {?StartDate}
  and created_date < {?EndDate}

Instead of setting the StartDate and EndDate parameters to date fields and having to update the parameters sent to the report each month you could set the parameters to string and pass the following SQL in to each parameter:

StartDate = add_months(trunc(sysdate, 'mm'), -1)
EndDate = trunc(sysdate, 'mm')

This way each time the scheduled report runs it automatically determines the correct date per开发者_开发技巧iod to run for. It works because Crystal reports passes the arguments in as literals and not as 'trunc(sysdate, 'mm')'.

Is it possible to pass SQL to a BIRT report parameter like this? I am using the BIRT plug-in for Eclipse and keep getting ORACLE errors that suggest BIRT is passing the parameters to the query as an SQL string complete with single quotes.

I do not think you want to do this (it is possible, but not advisable). You open your report up to SQL Injection attacks for one. So I can qualify as answering your question, you can set "this.query" to your parameter value inside the data set open event if you really want to do this.

More importantly, look to the root of the problem. What driver are you using? Ultimately the driver prepares and executes the query for BIRT. Can you execute the query outside BIRT using the same driver successfully? The other place to look at is the parameter set up on the report. Is the parameter strongly typed? It needs to be strongly typed to ensure it can be inserted into the query text correctly when it is subbed for the "?" placeholder you have in your root query.

Good Luck!