Error evaluating expression with multiple Database in iReport

databaseireportjasper-reports

I need to generate the report using iReport and I am trying to get the data from 2 different databases. I followed this article by (Bilal Siddiqui, author of JasperReports 3.6 Development Cookbook). But I am getting following error every time. Its working fine individually but when i use sub-report it starts giving me error.

Master Report :- 

<?xml version="1.0" encoding="UTF-8"?>
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="MasterReport" language="groovy" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20">
    <property name="ireport.zoom" value="1.0"/>
    <property name="ireport.x" value="0"/>
    <property name="ireport.y" value="0"/>
    <parameter name="projectName" class="java.lang.String">
        <defaultValueExpression><![CDATA[]]></defaultValueExpression>
    </parameter>
    <parameter name="SUBREPORT_DIR" class="java.lang.String" isForPrompting="false">
        <defaultValueExpression><![CDATA["D:\\rEPORTS\\"]]></defaultValueExpression>
    </parameter>
    <parameter name="connectionParam" class="java.sql.Connection" isForPrompting="false">
        <defaultValueExpression><![CDATA[java.sql.DriverManager.getConnection("jdbc:mysql://localhost/liferay_6.0.6", "root", "root")]]></defaultValueExpression>
    </parameter>
    <queryString>
        <![CDATA[QUERY]]>
    </queryString>
    <field name="EMAIL" class="java.lang.String"/>
    <background>
        <band splitType="Stretch"/>
    </background>
    <title>
        <band height="79" splitType="Stretch"/>
    </title>
    <pageHeader>
        <band height="35" splitType="Stretch"/>
    </pageHeader>
    <columnHeader>
        <band height="32" splitType="Stretch">
            <staticText>
                <reportElement x="0" y="2" width="100" height="20"/>
                <textElement textAlignment="Center" verticalAlignment="Middle">
                    <font size="12"/>
                </textElement>
                <text><![CDATA[EMAIL]]></text>
            </staticText>
            <staticText>
                <reportElement x="126" y="2" width="100" height="20"/>
                <textElement textAlignment="Center" verticalAlignment="Middle">
                    <font size="12"/>
                </textElement>
                <text><![CDATA[Work Station Name]]></text>
            </staticText>
            <staticText>
                <reportElement x="260" y="2" width="100" height="20"/>
                <textElement textAlignment="Center" verticalAlignment="Middle">
                    <font size="12"/>
                </textElement>
                <text><![CDATA[Work Station Type]]></text>
            </staticText>
        </band>
    </columnHeader>
    <detail>
        <band height="100" splitType="Stretch">
            <textField>
                <reportElement x="0" y="0" width="100" height="20"/>
                <textElement/>
                <textFieldExpression><![CDATA[$F{EMAIL}]]></textFieldExpression>
            </textField>
            <subreport>
                <reportElement x="271" y="0" width="200" height="100"/>
                <subreportParameter name="emailAddress">
                    <subreportParameterExpression><![CDATA[$F{EMAIL}]]></subreportParameterExpression>
                </subreportParameter>
                <connectionExpression><![CDATA[$P{connectionParam}]]></connectionExpression>
                <subreportExpression><![CDATA[$P{SUBREPORT_DIR} + "WorkstationSubReport.jasper"]]></subreportExpression>
            </subreport>
        </band>
    </detail>
    <columnFooter>
        <band height="45" splitType="Stretch"/>
    </columnFooter>
    <pageFooter>
        <band height="54" splitType="Stretch"/>
    </pageFooter>
    <summary>
        <band height="42" splitType="Stretch"/>
    </summary>
</jasperReport>

Error filling print… Error evaluating expression :  Source text : java.sql.DriverManager.getConnection("jdbc:mysql://localhost:3306/liferay_6.0.6","root","root") 

net.sf.jasperreports.engine.fill.JRExpressionEvalException: Error evaluating expression :  Source text : java.sql.DriverManager.getConnection("jdbc:mysql://localhost:3306/liferay_6.0.6","root","root") 

Best Answer

Try this steps for testing master report and subreport in iReport:

  1. Create Connection in iReport and test it. Don't forget to add jar file (jdbc driver) to the application (iReport in this test case) classpath.
  2. Create master report with query. Test it with iReport connection.
  3. Create subreport with query. Test it with iReport connection.
  4. Add Subreport component to the master report.
  5. Set subreport's subreportExpression and subreportParameterExpression expressions in master report.
  6. Create parameter of java.sql.Connection type in master report. Set defaultValueExpression for it. Use this parameter in the subreport's connectionExpression expression of master report.
  7. Compile subreport and master report.
  8. Test master report via Preview button in iReport.

The snippet of master report:

<?xml version="1.0" encoding="UTF-8"?>
<jasperReport ...>
    <parameter name="SUBREPORT_DIR" class="java.lang.String" isForPrompting="false">
        <defaultValueExpression><![CDATA["<some_path>"]]></defaultValueExpression>
    </parameter>
    <parameter name="connectionParam" class="java.sql.Connection" isForPrompting="false">
        <defaultValueExpression><![CDATA[java.sql.DriverManager.getConnection("jdbc:oracle:thin:@//<server_name>:<server_port>/<SID_name>", "<user>", "<password>")]]></defaultValueExpression>
    </parameter>
    <queryString>
        <![CDATA[SELECT id, name FROM main_table]]>
    </queryString>
    <field name="ID" class="java.lang.String"/>
    <field name="NAME" class="java.lang.String"/>
    <title>
        <band height="79" splitType="Stretch"/>
    </title>
    <detail>
        <band height="125" splitType="Stretch">
            <textField>
                <reportElement x="0" y="0" width="100" height="20"/>
                <textElement/>
                <textFieldExpression><![CDATA[$F{ID}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="0" y="20" width="100" height="20"/>
                <textElement/>
                <textFieldExpression><![CDATA[$F{NAME}]]></textFieldExpression>
            </textField>
            <subreport>
                <reportElement x="259" y="25" width="200" height="100"/>
                <subreportParameter name="masterId">
                    <subreportParameterExpression><![CDATA[$F{ID}]]></subreportParameterExpression>
                </subreportParameter>
                <connectionExpression><![CDATA[$P{connectionParam}]]></connectionExpression>

                <subreportExpression><![CDATA[$P{SUBREPORT_DIR} + "several_connections_subreport1.jasper"]]></subreportExpression>
            </subreport>
        </band>
    </detail>
</jasperReport>

The snippet of subreport:

<?xml version="1.0" encoding="UTF-8"?>
<jasperReport ...>
    <parameter name="masterId" class="java.lang.String"/>
    <queryString>
        <![CDATA[SELECT details, id FROM details_table where masterId =$P{masterId}]]>
    </queryString>
    <field name="DETAILS" class="java.lang.String"/>
    <field name="ID" class="java.lang.String"/>
    <title>
        <band height="50">
            <textField>
                <reportElement x="176" y="20" width="100" height="20"/>
                <textElement/>
                <textFieldExpression><![CDATA[$P{masterId}]]></textFieldExpression>
            </textField>
        </band>
    </title>
    <detail>
        <band height="125" splitType="Stretch">
            <textField>
                <reportElement x="57" y="17" width="100" height="20"/>
                <textElement/>
                <textFieldExpression><![CDATA[$F{DETAILS}]]></textFieldExpression>
            </textField>
        </band>
    </detail>
</jasperReport>
Related Topic