Query inside Jasper Reports

Manish Sahni

Ranch Hand

Posts: 41

I like...

posted 13 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Hello Ranchers,

I am new to iReport. I request you guys to assist me to solve the problem.

Scenario:
I am using jasper to generate reports.
I want to have 2 queries inside the jrxml file. Depends on the parameter one of the query has to be selected and report has to be generated.
The parameters are also dynamic..

With this above i could not make it.. i do not know how to proceed... Any help would be appreciated!!!

Thanks

Kaustubh Sharma

Greenhorn

Posts: 28

posted 13 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

hey I have got some code for you may be this will help you...........

<?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="RptCaseStatus" pageWidth="842" pageHeight="595" orientation="Landscape" columnWidth="802" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" isSummaryNewPage="true">
	<parameter name="strQry" class="java.lang.String">
		<defaultValueExpression><![CDATA[]]></defaultValueExpression>
	</parameter>
	<parameter name="strSender" class="java.lang.String"/>
	<parameter name="strQry2" class="java.lang.String"/>
	<parameter name="strBankName" class="java.lang.String"/>
	<parameter name="strReceiver" class="java.lang.String"/>
	<parameter name="strRel_Ref_No" class="java.lang.String"/>
	<parameter name="strImage" class="java.lang.String"/>
	<parameter name="strHeaderName" class="java.lang.String"/>
	<parameter name="strRptHdr1" class="java.lang.String"/>
	<parameter name="strRptHdr2" class="java.lang.String"/>
	<parameter name="strRptHdr3" class="java.lang.String"/>
	<parameter name="strCaseID" class="java.lang.String"/>
	<parameter name="strCreationDate" class="java.lang.String"/>
	<parameter name="strRefAmount" class="java.lang.String"/>
	<parameter name="strOwnerID" class="java.lang.String"/>
	<parameter name="strRe_OpenIndicator" class="java.lang.String"/>
	<parameter name="strMessageDirection" class="java.lang.String"/>
	<parameter name="strCase_Status" class="java.lang.String"/>
	<parameter name="strCase_Creator" class="java.lang.String"/>
	<parameter name="strUnderPayType" class="java.lang.String"/>
	<parameter name="strMsgDateTime" class="java.lang.String"/>
	<parameter name="strOrgAmount" class="java.lang.String"/>
	<parameter name="strOrgCurrency" class="java.lang.String"/>
	<parameter name="SUBREPORT_DIR" class="java.lang.String" isForPrompting="false">
		<defaultValueExpression><![CDATA["D:\\PPT\\WTW\\WEB-INF\\src\\com\\t8\\ReportEngine\\"]]></defaultValueExpression>
	</parameter>
	<parameter name="strDate" class="java.lang.String"/>
	<queryString>
		<![CDATA[$P!{strQry}]]>
	</queryString>
	<field name="Login_Name" class="java.lang.String">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="Caseaddlinfo_CaseId" class="java.lang.String">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="MessageExchanged_Currency" class="java.lang.String">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="MessageExchanged_MessageReference" class="java.lang.String">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="MessageExchanged_RefAmount" class="java.lang.Double">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="Caseaddlinfo_CaseCreationDate" class="java.sql.Timestamp">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="Caseaddlinfo_CaseStatus" class="java.lang.String">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="CaseDetails_ReOpenIndicator" class="java.lang.Boolean">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="PaymentInsEx_Interbnkstlmtamount" class="java.lang.Double">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="MessageExchanged_Senders" class="java.lang.String">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="MessageExchanged_Recievers" class="java.lang.String">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="Caseaddlinfo_CaseOwnerUUID" class="java.lang.String">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="PaymentInstructionExtract_originalMsgId" class="java.lang.String">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<title>
		<band height="71">
			<textField>
				<reportElement x="-32" y="32" width="801" height="35"/>
				<textElement textAlignment="Center" markup="none">
					
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$P{strHeaderName}]]></textFieldExpression>
			</textField>
			<line>
				<reportElement x="2" y="69" width="798" height="1"/>
			</line>
			<textField>
				<reportElement x="12" y="2" width="315" height="32"/>
				<textElement markup="none">
					
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$P{strBankName}]]></textFieldExpression>
			</textField>
			<image>
				<reportElement x="665" y="2" width="135" height="25"/>
				<imageExpression class="java.lang.String"><![CDATA[$P{strImage}]]></imageExpression>
			</image>
		</band>
	</title>
	<pageHeader>
		<band/>
	</pageHeader>
	<columnHeader>
		<band height="181">
			<staticText>
				<reportElement x="392" y="50" width="5" height="127"/>
				<textElement>
					
				</textElement>
				<text><![CDATA[:::::::::::::::::]]></text>
			</staticText>
			<textField>
				<reportElement mode="Opaque" x="176" y="71" width="150" height="27"/>
				<textElement textAlignment="Justified" markup="none">
					
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$P{strCreationDate}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement mode="Opaque" x="177" y="90" width="150" height="27"/>
				<textElement textAlignment="Justified" markup="none">
					
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$P{strCase_Creator}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement mode="Opaque" x="177" y="114" width="199" height="27"/>
				<textElement textAlignment="Justified" markup="none">
					
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$P{strRe_OpenIndicator}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement mode="Opaque" x="177" y="155" width="150" height="22"/>
				<textElement textAlignment="Justified" markup="none">
					
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$P{strOwnerID}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement mode="Opaque" x="176" y="134" width="150" height="27"/>
				<textElement textAlignment="Justified" markup="none">
					
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$P{strCase_Status}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement x="5" y="5" width="775" height="35"/>
				<textElement textAlignment="Center" markup="none">
					
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$P{strRptHdr1}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement x="176" y="48" width="150" height="20"/>
				<textElement textAlignment="Justified" markup="none">
					
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$P{strCaseID}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement x="477" y="48" width="181" height="20"/>
				<textElement textAlignment="Center"/>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{Caseaddlinfo_CaseId}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement x="476" y="70" width="182" height="20"/>
				<textElement textAlignment="Center"/>
				<textFieldExpression class="java.sql.Timestamp"><![CDATA[$F{Caseaddlinfo_CaseCreationDate}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement x="477" y="120" width="181" height="20"/>
				<textElement textAlignment="Center"/>
				<textFieldExpression class="java.lang.Boolean"><![CDATA[$F{CaseDetails_ReOpenIndicator}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement x="476" y="140" width="182" height="20"/>
				<textElement textAlignment="Center"/>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{Caseaddlinfo_CaseStatus}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement x="477" y="95" width="181" height="20"/>
				<textElement textAlignment="Center"/>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{Login_Name}]]></textFieldExpression>
			</textField>
			<line>
				<reportElement x="1" y="-41" width="798" height="1"/>
			</line>
			<line>
				<reportElement x="1" y="41" width="798" height="1"/>
			</line>
			<textField>
				<reportElement x="477" y="161" width="183" height="20"/>
				<textElement textAlignment="Center"/>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{Caseaddlinfo_CaseOwnerUUID}]]></textFieldExpression>
			</textField>
		</band>
	</columnHeader>
	<detail>
		<band height="224">
			<textField>
				<reportElement mode="Opaque" x="176" y="39" width="162" height="27"/>
				<textElement textAlignment="Justified" markup="none">
					
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$P{strRel_Ref_No}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement mode="Opaque" x="176" y="117" width="162" height="27"/>
				<textElement textAlignment="Justified" markup="none">
					
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$P{strReceiver}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement mode="Opaque" x="176" y="195" width="150" height="27"/>
				<textElement textAlignment="Justified" markup="none">
					
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$P{strRefAmount}]]></textFieldExpression>
			</textField>
			<staticText>
				<reportElement x="392" y="42" width="5" height="182"/>
				<textElement>
					
				</textElement>
				<text><![CDATA[::::::::::::::::::::::]]></text>
			</staticText>
			<textField>
				<reportElement mode="Opaque" x="177" y="168" width="162" height="27"/>
				<textElement textAlignment="Justified" markup="none">
					
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$P{strOrgAmount}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement mode="Opaque" x="177" y="144" width="162" height="27"/>
				<textElement textAlignment="Justified" markup="none">
					
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$P{strOrgCurrency}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement x="490" y="201" width="182" height="20"/>
				<textElement textAlignment="Center"/>
				<textFieldExpression class="java.lang.Double"><![CDATA[$F{MessageExchanged_RefAmount}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement x="489" y="174" width="182" height="20"/>
				<textElement textAlignment="Center"/>
				<textFieldExpression class="java.lang.Double"><![CDATA[$F{PaymentInsEx_Interbnkstlmtamount}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement x="488" y="116" width="183" height="20"/>
				<textElement textAlignment="Center"/>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{MessageExchanged_Recievers}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement x="463" y="45" width="228" height="20"/>
				<textElement textAlignment="Center"/>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{MessageExchanged_MessageReference}]]></textFieldExpression>
			</textField>
			<line>
				<reportElement x="5" y="36" width="798" height="1"/>
			</line>
			<textField>
				<reportElement x="475" y="143" width="208" height="20"/>
				<textElement textAlignment="Center"/>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{MessageExchanged_Currency}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement x="177" y="66" width="161" height="20"/>
				<textElement>
					
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$P{strUnderPayType}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement x="464" y="65" width="226" height="20"/>
				<textElement textAlignment="Center"/>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{PaymentInstructionExtract_originalMsgId}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement mode="Opaque" x="177" y="92" width="162" height="27"/>
				<textElement textAlignment="Justified" markup="none">
					
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$P{strSender}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement x="464" y="91" width="224" height="20"/>
				<textElement textAlignment="Center"/>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{MessageExchanged_Senders}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement x="10" y="2" width="792" height="35"/>
				<textElement textAlignment="Center" markup="none">
					
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$P{strRptHdr2}]]></textFieldExpression>
			</textField>
		</band>
	</detail>
	<lastPageFooter>
		<band height="63">
			<textField pattern="" isBlankWhenNull="false">
				<reportElement key="textField" x="16" y="16" width="209" height="19"/>
				<box>
					<topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<bottomPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
				</box>
				<textElement>
					
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$P{strDate}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement x="4" y="38" width="69" height="20"/>
				<textElement textAlignment="Center" markup="none">
					
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$P{strCaseID}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement x="85" y="40" width="73" height="17"/>
				<textElement/>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{Caseaddlinfo_CaseId}]]></textFieldExpression>
			</textField>
			<textField pattern="" isBlankWhenNull="false">
				<reportElement key="textField" x="523" y="18" width="170" height="19"/>
				<box>
					<topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<bottomPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
				</box>
				<textElement textAlignment="Right">
					
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA["Page " + $V{PAGE_NUMBER} + " of "]]></textFieldExpression>
			</textField>
			<textField evaluationTime="Report" pattern="" isBlankWhenNull="false">
				<reportElement key="textField" x="704" y="18" width="36" height="19"/>
				<box>
					<topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<bottomPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
				</box>
				<textElement>
					
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA["" + $V{PAGE_NUMBER}]]></textFieldExpression>
			</textField>
			<staticText>
				<reportElement x="78" y="39" width="4" height="18"/>
				<textElement>
					
				</textElement>
				<text><![CDATA[:::::::::::::::::]]></text>
			</staticText>
			<line>
				<reportElement x="0" y="9" width="802" height="1"/>
			</line>
			<line>
				<reportElement x="-1" y="5" width="802" height="1"/>
			</line>
			<textField pattern="" isBlankWhenNull="false">
				<reportElement key="textField" x="544" y="40" width="255" height="19"/>
				<box>
					<topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<bottomPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
				</box>
				<textElement textAlignment="Center">
					
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA["This report is created by: "+ $F{Login_Name}]]></textFieldExpression>
			</textField>
		</band>
	</lastPageFooter>
	<summary>
		<band height="240">
			<subreport>
				<reportElement x="115" y="12" width="238" height="68"/>
				<subreportParameter name="strMsgDateTime">
					<subreportParameterExpression><![CDATA[$P{strMsgDateTime}]]></subreportParameterExpression>
				</subreportParameter>
				<subreportParameter name="strCase_Status">
					<subreportParameterExpression><![CDATA[$P{strCase_Status}]]></subreportParameterExpression>
				</subreportParameter>
				<subreportParameter name="strQry">
					<subreportParameterExpression><![CDATA[$P{strQry}]]></subreportParameterExpression>
				</subreportParameter>
				<subreportParameter name="strMessageDirection">
					<subreportParameterExpression><![CDATA[$P{strMessageDirection}]]></subreportParameterExpression>
				</subreportParameter>
				<subreportParameter name="strOrgAmount">
					<subreportParameterExpression><![CDATA[$P{strOrgAmount}]]></subreportParameterExpression>
				</subreportParameter>
				<subreportParameter name="strRefAmount">
					<subreportParameterExpression><![CDATA[$P{strRefAmount}]]></subreportParameterExpression>
				</subreportParameter>
				<subreportParameter name="strSender">
					<subreportParameterExpression><![CDATA[$P{strSender}]]></subreportParameterExpression>
				</subreportParameter>
				<subreportParameter name="strRptHdr1">
					<subreportParameterExpression><![CDATA[$P{strRptHdr1}]]></subreportParameterExpression>
				</subreportParameter>
				<subreportParameter name="strReceiver">
					<subreportParameterExpression><![CDATA[$P{strReceiver}]]></subreportParameterExpression>
				</subreportParameter>
				<subreportParameter name="SUBREPORT_DIR">
					<subreportParameterExpression><![CDATA[$P{SUBREPORT_DIR}]]></subreportParameterExpression>
				</subreportParameter>
				<subreportParameter name="strRptHdr2">
					<subreportParameterExpression><![CDATA[$P{strRptHdr2}]]></subreportParameterExpression>
				</subreportParameter>
				<subreportParameter name="strHeaderName">
					<subreportParameterExpression><![CDATA[$P{strHeaderName}]]></subreportParameterExpression>
				</subreportParameter>
				<subreportParameter name="strCaseID">
					<subreportParameterExpression><![CDATA[$P{strCaseID}]]></subreportParameterExpression>
				</subreportParameter>
				<subreportParameter name="strRptHdr3">
					<subreportParameterExpression><![CDATA[$P{strRptHdr3}]]></subreportParameterExpression>
				</subreportParameter>
				<subreportParameter name="strUnderPayType">
					<subreportParameterExpression><![CDATA[$P{strUnderPayType}]]></subreportParameterExpression>
				</subreportParameter>
				<subreportParameter name="strCase_Creator">
					<subreportParameterExpression><![CDATA[$P{strCase_Creator}]]></subreportParameterExpression>
				</subreportParameter>
				<subreportParameter name="strCreationDate">
					<subreportParameterExpression><![CDATA[$P{strCreationDate}]]></subreportParameterExpression>
				</subreportParameter>
				<subreportParameter name="strOwnerID">
					<subreportParameterExpression><![CDATA[$P{strOwnerID}]]></subreportParameterExpression>
				</subreportParameter>
				<subreportParameter name="strRe_OpenIndicator">
					<subreportParameterExpression><![CDATA[$P{strRe_OpenIndicator}]]></subreportParameterExpression>
				</subreportParameter>
				<subreportParameter name="strImage">
					<subreportParameterExpression><![CDATA[$P{strImage}]]></subreportParameterExpression>
				</subreportParameter>
				<subreportParameter name="strQry2">
					<subreportParameterExpression><![CDATA[$P{strQry2}]]></subreportParameterExpression>
				</subreportParameter>
				<subreportParameter name="strRel_Ref_No">
					<subreportParameterExpression><![CDATA[$P{strRel_Ref_No}]]></subreportParameterExpression>
				</subreportParameter>
				<connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
				<subreportExpression class="java.lang.String"><![CDATA["D:\\PPT\\WTW\\WEB-INF\\src\\com\\t8\\ReportEngine\\CaseDetailsExportReport_subreport3.jasper"]]></subreportExpression>
			</subreport>
		</band>
	</summary>
</jasperReport>

if this will not work for you please let me know........

Jaikiran Pai

Sheriff

Posts: 10445

227

I like...

posted 13 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

kaustubh sharma wrote:hey I have got some code for you may be this will help you...........

While posting logs or xml content or code, please remember to wrap it in a code block by using the Code button in the message editor window. Please also use the Preview button to ensure that your post is correctly formatted.

I've fixed your post for now.

[My Blog] [JavaRanch Journal]

Kaustubh Sharma

Greenhorn

Posts: 28

posted 13 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

thanks boss from the next time I will take care of it but please don't block my reply option.....

Did you see how Paul cut 87% off of his electric heat bill with 82 watts of micro heaters?