Externalize BIRT queries

Question

Hi, Is there a way to externalize report queries for BIRT reports. We need to support multiple database engines and so our queries are different depending on the underlying database. I would like to use a config parameter to tell BIRT report to use a specific query file

Answer 1

I do not know of a way to do this out of the box. You could probably dream up some fairly complex scripting to fire off on the onLoad event of the Data Set.

How about placing the same stored procedure in each DB? THen you can parameterize the data connection information (On the Edit page for the report Data Source) and dynamically direct your report to query a specific data source. As long as the stored proc is on all instances, you will get the correct data from the correct source.

Answer 2

Sure you can. If you code up some Javascript in the report itself, it can access files on the disk to retrieve the textual queries and modify the query before it's executed.

The event you need to code for is beforeOpen on the data source. We actually use this for wildcarding parameters by detecting if they're set to "*" and dynamically adjusting the SQL query, changing it from:

select A from B where C = ?

to:

select A from B where ((C = ?) or (1==1))

The ugly modification is just so we don't have to worry about changing the positional parameters.

You can read a line from a disk file and change the query with something like:

try {
  var fip0 = new Packages.java.io.FileInputStream("/query.txt");
  try {
    var fip1 = new Packages.java.io.DataInputStream(fip0);
    try {
      queryText = fip1.readLine() + "";
    } catch(e1) {}
    fip1.close();
  } catch(e2) {}
  fip0.close();
} catch(e3) {}

although you should probably have better error checking than that :-) I removed it as it's (1) somewhat large; and (2) somewhat proprietary.