Dynamic querystring in JRXML [closed]

Question

I'm trying to build a report that would be smart enough to modify slightly its sql query based on an input parameter of some sort.

For example if that special modifying parameter value is "1", it adds a field in the select and adds a group by clause to the query.

I've looked into java expressions, but they don't seem to be supported in the queryString tag of the jrxml. Also tried to make a variable containing the java expression and use that variable in the queryString tag... That didn't work either!

Right now I'm thinking of maybe have a stored procedure with all that logic and simply have the jrxml calling that stored procedure with the modifying input parameter, but the project I'm working on doesn't seem to have a whole lot of stored proc, so I'd like to see if there are other solutions before I go down that path.

Thanks for your help.

---

Thank you guys for your help, much apprieciated. However I found another way to go about it, and posted it for information: here

Answer 1

I've done it using stored procedures which are just fine for these kinds of stuff. Otherwise you may switch to Java. Just grab the data from the database and according to the user provided parameters filter it, group it and send as a collection of beans to the Jasper report which will do the rendering.

Answer 2

JasperDesign actually lets you modify portions of your jrxml document. So say you have a package "reports" where you store your report built either by hand or by a tool like iReport. As long as your query is defined in the tag <queryString> the following will work allowing you to change the query on the fly:

try {
    String fileName = getClass().getClassLoader().getResource("com/foo/myproject/reports/TestReport.jrxml").getFile();
    File theFile = new File(fileName);
    JasperDesign jasperDesign = JRXmlLoader.load(theFile);

    //Build a new query
    String theQuery = "SLECT * FROM myTable WHERE ...";

    // update the data query
    JRDesignQuery newQuery = new JRDesignQuery();
    newQuery.setText(theQuery);
    jasperDesign.setQuery(newQuery);

    JasperReport jasperReport = JasperCompileManager.compileReport(jasperDesign);
    Connection conn = MyDatabaseClass.getConnection();
    JasperPrint jasperPrint = JasperFillManager.fillReport(jasperReport, null, conn);
    JasperViewer.viewReport(jasperPrint);
} catch (Exception ex) {
    String connectMsg = "Could not create the report " + ex.getMessage() + " " + ex.getLocalizedMessage();
    System.out.println(connectMsg);
}

With something like this you can create a member variable of your class that holds the new query and build it with whatever user constrains desired. Then at view time just modify the design.

-Jeff

Answer 3

JasperDesign helped me to solve the problem of building dynamic query on Jrxml file.

To build the Dynamic SQL, I was using the Squiggle(Google Code) to build the SQL dynamically. Thanks jeff