Dynamic querystring in JRXML

Question

Hi everyone,

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.

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

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

Answer 4

Hey! I am working on jrxml to create dynamic re0ports. I have parameterized the columns i.e. the jrxml for that report can be used to generate other reports as well.

However, i have not managed to make the fields flexible. That is, if the user selects 4 columns it would work but if 1 or 2 or 3 columns are selected, it gives an error since the field names are unidentified.

Please post a solution urgently if something like a default expression for fieldname can be created or a for loop/java script can be used.

Moreover, how can jasper designer be exactly used to achieve this?

The jrxml is as follows:

<parameter name="reportTitle" class="java.lang.String"/>
<parameter name="author" class="java.lang.String"/>
<parameter name="startDate" class="java.lang.String"/>

**<parameter name="C1" class="java.lang.String">
    <defaultValueExpression>
        new java.lang.String("")
    </defaultValueExpression>
</parameter>
<parameter name="C2" class="java.lang.String">
    <defaultValueExpression>
        new java.lang.String("")
    </defaultValueExpression>
</parameter>
<parameter name="C3" class="java.lang.String">
    <defaultValueExpression>
        new java.lang.String("")
    </defaultValueExpression>
</parameter>
<parameter name="C4" class="java.lang.String">
    <defaultValueExpression>
        new java.lang.String("default parameter value")
    </defaultValueExpression>
</parameter>**

<field name="COLUMN_1" class="java.lang.Integer"/>
<field name="COLUMN_2" class="java.lang.Integer"/>
<field name="COLUMN_3" class="java.lang.Integer"/>
<field name="COLUMN_4" class="java.lang.Integer"/>

<title>
    <band height="60">
        <textField>
            <reportElement x="0" y="10" width="500" height="40"/>
            <textElement textAlignment="Center">
                <font size="24"/>
            </textElement>
            <textFieldExpression class="java.lang.String">
                <![CDATA[$P{reportTitle}]]>
            </textFieldExpression>
        </textField>
        <textField>
            <reportElement x="0" y="40" width="500" height="20"/>
            <textElement textAlignment="Center"/>
            <textFieldExpression class="java.lang.String">
                <![CDATA["Run by: " + $P{author}
                    + " on " + $P{startDate}]]>
            </textFieldExpression>
        </textField>
    </band>
</title>

        <textField>
            <reportElement x="0" y="5" width="170" height="15"/>
            <textFieldExpression class="java.lang.String">
                <![CDATA[$P{C1}]]>
            </textFieldExpression>
        </textField>

        <textField>
            <reportElement x="70" y="5" width="170" height="15"/>
          <textFieldExpression class="java.lang.String">
                <![CDATA[$P{C2}]]>
            </textFieldExpression>
        </textField>

        <textField>
            <reportElement x="150" y="5" width="150" height="15"/>
          <textFieldExpression class="java.lang.String">
                <![CDATA[$P{C3}]]>
            </textFieldExpression>
        </textField>

        <textField>
            <reportElement x="300" y="5" width="150" height="15"/>
          <textFieldExpression class="java.lang.String">
                <![CDATA[$P{C4}]]>
            </textFieldExpression>

        </textField>


        </band>
</columnHeader>

        <textField>
            <reportElement x="5" y="0" width="50" height="15"/>
            <textElement/>
            <textFieldExpression class="java.lang.Integer">
                <![CDATA[$F{COLUMN_1}]]>
            </textFieldExpression>
        </textField>

        <textField>
            <reportElement x="90" y="0" width="150" height="15"/>
            <textElement/>
            <textFieldExpression class="java.lang.Integer">
                <![CDATA[$F{COLUMN_2}]]>
            </textFieldExpression>
        </textField>

        <textField>
            <reportElement x="170" y="0" width="50" height="15"/>
            <textElement/>
            <textFieldExpression class="java.lang.Integer">
                <![CDATA[$F{COLUMN_3}]]>
            </textFieldExpression>
        </textField>

        <textField>
            <reportElement x="320" y="0" width="150" height="15"/>
            <textElement/>
            <textFieldExpression class="java.lang.Integer">
                <![CDATA[$F{COLUMN_4}]]>
            </textFieldExpression>
        </textField>

    </band>
</detail>

Answer 5

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