iReports: Pass SQL statement as a parameter

Classic Servoy
1

Can the actual SQL statement be passed as a parameter to the report?

This way, if I use a form to help the user select from available search options for the report, I can then constuct the SQL statement and pass it to the report so it generates with the proper criteria.

This is different from passing field variables, as not all may have been used by the user in this report.

2

Yes it is possible. I recall having seen that before. Let me go digg where I read about that.

3

Found it! Scott Butler emailed this a while a go to me:

Lets pretend we're using Servoy's UDM database...

create your report, and just use a static query, like "SELECT * FROM companies"
add your fields, etc to the report
create a parameter, we'll call it "sql_input"  as java.lang.String
set default value of parameter to "SELECT * FROM companies"
Now the trick, modify your report query to be $P!{sql_input}
NOTE, the trick is the exclamation point after the P (I did a lot of googling to find this)

And that is it.  You can even run it in iReport and test it.  When prompted for a parameter, you can type in a SQL query in quotes, or just use default.  Then the Servoy code to run it looks like this...

var params = new java.util.HashMap()
var sql = "SELECT * FROM companies WHERE companies.company_id = " + forms.frm_company.company_id + " order by companies.company_id"
params.put('sql_input', sql)
plugins.jasperPluginRMI.jasperReport('udm','sql_param_2.jrxml',null,'view', params);
4

Excellent. Just what I needed.