How to use external query for a sub-report?

[kaung_1]

I am trying to use external queries to create a report(s) that can support multiple SQL databases - mysql and oracle. My idea is I will create multiple queries - one for each database type, and a single reportUnit. Depending on which db type a customer has, I will link the right queries to the reportUnit. 

 

But my reportUnit contains subreports and I cannot find a way to set an external query to a subreport. I can set only one external query to a reportUnit and that query seem to override only the main_jrxml of the reportUnit. SubReports are not affected by external query.

 

Here, I find docs about external queries but there is no mention how it behaves with subreports.

http://community.jaspersoft.com/documentation/jasperreports-server-user-guide/defining-query

 

I tried the following:

* If only I could pass a report's query to a subreport via subreport tag, it would be a solution. But subreports tag only support passing report connection but not query. 

 

There seem to be no other way to me. Is there any way that I can externalize they query from a subreport? Thanks in advance. 

[akosolapov83]

You can pass your sql query as string parameter. And then use it like this:

<queryString>
<![CDATA[$P!{SQL_QUERY}]]>
</queryString>

 

[kaung_1]

That SQL_QUERY string itself will contain parameters. e.g. If my report is to get all users from a certain country, there will be a "where users = $P{country}" somewhere in that SQL_QUERY string. Would jasper further substitute these paramters or will the string get sent to database lterally as it is? Would be amazing if substituation happens.... (I'll test it out soon but I doubt parameter substitution will happen)

 

[reportdev]

Jasper Server supports using multiple databases on a single report.

Default Data adapters 

1. Create a datasource xml file with username, password and connection strings
2. Place your datasource xml file on the Tomcat webapps directory and related jdbc driver in the lib folder.
3. On the Report Dataset properties - default data adapter, reference the datasource xml file on the webapps directory.