Query inside Jasper Reports

[Manish Sahni]

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..

  

          
      
   3. <parameter name="Name" class="java.lang.String"/>  
   4. <parameter name="Number" class="java.lang.String"/>  
   5. <parameter name="City" class="java.lang.String"/>  
   6.   
   7. <parameter name="SelectQuery" class="java.lang.String" >  
   8. <defaultValueExpression><![CDATA[($P{Number}!=null ? query1 : query2)]]></defaultValueExpression>  
   9. </parameter>  
  10.   
  11. <queryString>  
  12.         <![CDATA[ $P{SelectQuery}]]>  
  13. </queryString>  

<parameter name="Name" class="java.lang.String"/> <parameter name="Number" class="java.lang.String"/> <parameter name="City" class="java.lang.String"/> <parameter name="SelectQuery" class="java.lang.String" > <defaultValueExpression><![CDATA[($P{Number}!=null ? query1 : query2)]]></defaultValueExpression> </parameter> <queryString> <![CDATA[ $P{SelectQuery}]]> </queryString>
  

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

Thanks

[Kaustubh Sharma]

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">
					<font size="24" isBold="true"/>
				</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">
					<font size="14" isBold="true"/>
				</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>
					<font size="14" isBold="true"/>
				</textElement>
				<text><![CDATA[:::::::::::::::::]]></text>
			</staticText>
			<textField>
				<reportElement mode="Opaque" x="176" y="71" width="150" height="27"/>
				<textElement textAlignment="Justified" markup="none">
					<font size="15" isBold="true"/>
				</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">
					<font size="15" isBold="true"/>
				</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">
					<font size="15" isBold="true"/>
				</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">
					<font size="15" isBold="true"/>
				</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">
					<font size="15" isBold="true"/>
				</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">
					<font size="24" isBold="true"/>
				</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">
					<font size="15" isBold="true"/>
				</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">
					<font size="15" isBold="true"/>
				</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">
					<font size="15" isBold="true"/>
				</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">
					<font size="15" isBold="true"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$P{strRefAmount}]]></textFieldExpression>
			</textField>
			<staticText>
				<reportElement x="392" y="42" width="5" height="182"/>
				<textElement>
					<font size="14" isBold="true"/>
				</textElement>
				<text><![CDATA[::::::::::::::::::::::]]></text>
			</staticText>
			<textField>
				<reportElement mode="Opaque" x="177" y="168" width="162" height="27"/>
				<textElement textAlignment="Justified" markup="none">
					<font size="15" isBold="true"/>
				</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">
					<font size="15" isBold="true"/>
				</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>
					<font size="15" isBold="true"/>
				</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">
					<font size="15" isBold="true"/>
				</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">
					<font size="24" isBold="true"/>
				</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>
					<font size="10"/>
				</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">
					<font size="10" isBold="true"/>
				</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">
					<font size="10"/>
				</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>
					<font size="10"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA["" + $V{PAGE_NUMBER}]]></textFieldExpression>
			</textField>
			<staticText>
				<reportElement x="78" y="39" width="4" height="18"/>
				<textElement>
					<font size="12" isBold="true"/>
				</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">
					<font size="10"/>
				</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]

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.

[Kaustubh Sharma]

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