Hello All,
I am new to jaspersoft studio. I am trying to generate a sub-report where for each row in main report there is a subreport of several rows. But I want to pass a paraeter to identify for which row I am displaying sub report. So I need to pass paramter different for every row. But I am unable to do it. I followed below steps but i am uanble to pass value of main report to sub-report.
1] click on subreport that is added in main report. on right hand side in properties pane selecte subreport button. Click "Edit Parameters" button. added a paramter named "master_id" and set it to Field value for ex $F{master_id}.
2] click finish.
3] in subreport right click on tabledataset and click Dataset and query dailog and added query with condition tablename.id = $P{master_id}
When I run report it throwsbelow exception :
net.sf.jasperreports.engine.JRException: net.sf.jasperreports.engine.JRException: Resource not found at: Coffee_Table_Based_SubReport.jasper.
at com.jaspersoft.studio.editor.preview.view.control.ReportControler.fillReport(ReportControler.java:530)
at com.jaspersoft.studio.editor.preview.view.control.ReportControler.access$20(ReportControler.java:505)
at com.jaspersoft.studio.editor.preview.view.control.ReportControler$5.run(ReportControler.java:386)
at org.eclipse.core.internal.jobs.Worker.run(Worker.java:55)
Caused by: net.sf.jasperreports.engine.JRException: Resource not found at: Coffee_Table_Based_SubReport.jasper.
at net.sf.jasperreports.repo.RepositoryUtil.getResourceFromLocation(RepositoryUtil.java:153)
at net.sf.jasperreports.repo.RepositoryUtil.getReport(RepositoryUtil.java:112)
at net.sf.jasperreports.engine.fill.JRFillSubreport.loadReport(JRFillSubreport.java:398)
at net.sf.jasperreports.engine.fill.JRFillSubreport.evaluateReport(JRFillSubreport.java:365)
at net.sf.jasperreports.engine.fill.JRFillSubreport.evaluateSubreport(JRFillSubreport.java:427)
at net.sf.jasperreports.engine.fill.JRFillSubreport.evaluate(JRFillSubreport.java:341)
at net.sf.jasperreports.engine.fill.JRFillElementContainer.evaluate(JRFillElementContainer.java:381)
at net.sf.jasperreports.engine.fill.JRFillBand.evaluate(JRFillBand.java:500)
at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillColumnBand(JRVerticalFiller.java:2022)
at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillDetail(JRVerticalFiller.java:748)
at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReportStart(JRVerticalFiller.java:255)
at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReport(JRVerticalFiller.java:115)
at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:580)
at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFill.run(BaseFillHandle.java:123)
at java.lang.Thread.run(Thread.java:745)
I think subreport is not able to get the parameter that is passed from main report. Can anyone please help me about this. Below m attaching the jrxml of main report
<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.2.2.final using JasperReports Library version 6.2.2 -->
<!-- 2017-02-13T17:10:26 -->
<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="Coffee_Landscape" pageWidth="5000" pageHeight="4000" orientation="Landscape" columnWidth="4960" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="df013db5-f76e-44d3-b0df-bcbc46d93160">
<property name="com.jaspersoft.studio.data.sql.tables" value=""/>
<property name="com.jaspersoft.studio.data.defaultdataadapter" value="webinteractive_1"/>
<property name="com.jaspersoft.studio.unit." value="pixel"/>
<property name="com.jaspersoft.studio.unit.pageHeight" value="pixel"/>
<property name="com.jaspersoft.studio.unit.pageWidth" value="pixel"/>
<property name="com.jaspersoft.studio.unit.topMargin" value="pixel"/>
<property name="com.jaspersoft.studio.unit.bottomMargin" value="pixel"/>
<property name="com.jaspersoft.studio.unit.leftMargin" value="pixel"/>
<property name="com.jaspersoft.studio.unit.rightMargin" value="pixel"/>
<property name="com.jaspersoft.studio.unit.columnWidth" value="pixel"/>
<property name="com.jaspersoft.studio.unit.columnSpacing" value="pixel"/>
<style name="Title" fontName="Times New Roman" fontSize="50" isBold="true"/>
<style name="SubTitle" forecolor="#736343" fontName="Arial" fontSize="18"/>
<style name="Column header" forecolor="#666666" fontName="Arial" fontSize="12" isBold="true"/>
<style name="Detail" backcolor="#FCFCE6" fontName="Arial" fontSize="12"/>
<style name="Row" mode="Opaque" backcolor="#F0EADF">
<conditionalStyle>
<conditionExpression><![CDATA[$V{REPORT_COUNT}%2 == 0]]></conditionExpression>
<style backcolor="#E6DAC3"/>
</conditionalStyle>
</style>
<style name="Crosstab_CH" mode="Opaque" backcolor="#F0F8FF">
<box>
<pen lineWidth="0.5" lineColor="#000000"/>
<topPen lineWidth="0.5" lineColor="#000000"/>
<leftPen lineWidth="0.5" lineColor="#000000"/>
<bottomPen lineWidth="0.5" lineColor="#000000"/>
<rightPen lineWidth="0.5" lineColor="#000000"/>
</box>
</style>
<parameter name="fileCategory" class="java.lang.String"/>
<parameter name="dateRecieved" class="java.util.Date">
<defaultValueExpression><![CDATA[NOW( )]]></defaultValueExpression>
</parameter>
<parameter name="fileCategory_value" class="java.lang.String">
<defaultValueExpression><![CDATA[($P{fileCategory}==null? "1:1":" and file_master.file_type=' "+$P{fileCategory} + " ' " )]]></defaultValueExpression>
</parameter>
<parameter name="masterId_param" class="java.lang.Integer" isForPrompting="false"/>
<parameter name="masterId" class="java.lang.Integer" isForPrompting="false"/>
<queryString>
<![CDATA[SELECT public.detail.status,
public.detail.file_id,
public.detail.priority,
public.detail.document_type,
public.detail.document_id,
public.detail.due_date,
public.detail.master_id,
public.detail.description,
public.detail.sender_first_name,
public.detail.sender_organization,
public.detail.sender_last_name,
public.detail.updated_on,
public.detail.sender_address_id,
public.detail.updated_by,
public.detail.id AS masterId,
public.detail.created_on,
public.detail.comments,
public.detail.priority AS masterPriority,
public.detail.created_by,
public.detail.sender_title,
public.detail.status AS masterStatus,
public.detail.address AS senderaddress,
public.detail.town AS sendertown,
public.detail.parish AS senderparish,
public.detail.country AS sendercountry,
public.detail.postal_code AS senderpostalcode,
public.detail.name AS documentName,
public.detail.status AS documentStatus
FROM public.detail,
]]>
</queryString>
<field name="status" class="java.lang.String"/>
<field name="file_id" class="java.lang.Long"/>
<field name="priority" class="java.lang.String"/>
<field name="document_type" class="java.lang.Long"/>
<field name="document_id" class="java.lang.Long"/>
<field name="due_date" class="java.sql.Timestamp"/>
<field name="master_id" class="java.lang.Long"/>
<field name="description" class="java.lang.String"/>
<field name="sender_first_name" class="java.lang.String"/>
<field name="sender_organization" class="java.lang.String"/>
<field name="sender_last_name" class="java.lang.String"/>
<field name="updated_on" class="java.sql.Timestamp"/>
<field name="sender_address_id" class="java.lang.Long"/>
<field name="updated_by" class="java.lang.String"/>
<field name="masterid" class="java.lang.Long"/>
<field name="created_on" class="java.sql.Timestamp"/>
<field name="comments" class="java.lang.String"/>
<field name="masterpriority" class="java.lang.String"/>
<field name="created_by" class="java.lang.String"/>
<field name="sender_title" class="java.lang.String"/>
<field name="masterstatus" class="java.lang.String"/>
<field name="senderaddress" class="java.lang.String"/>
<field name="sendertown" class="java.lang.String"/>
<field name="senderparish" class="java.lang.Long"/>
<field name="sendercountry" class="java.lang.String"/>
<field name="senderpostalcode" class="java.lang.String"/>
<field name="documentname" class="java.lang.String"/>
<field name="documentstatus" class="java.lang.String"/>
<group name="IncomingMail">
<groupExpression><![CDATA[$F{master_id}]]></groupExpression>
</group>
<background>
<band splitType="Stretch"/>
</background>
<title>
<band height="136" splitType="Stretch">
<image>
<reportElement x="0" y="0" width="164" height="126" uuid="1c003177-754c-448f-8ce1-16868856f545"/>
<imageExpression><![CDATA["coffee.jpg"]]></imageExpression>
</image>
<staticText>
<reportElement style="Title" x="270" y="0" width="263" height="62" uuid="bc1ce1da-8232-46ea-be55-cec4abb986dd"/>
<textElement verticalAlignment="Middle"/>
<text><![CDATA[Coffee Title]]></text>
</staticText>
<staticText>
<reportElement style="SubTitle" x="303" y="62" width="196" height="22" uuid="f6a78448-8260-4445-a9e0-e3fb53b080d9"/>
<textElement>
<font fontName="Times New Roman"/>
</textElement>
<text><![CDATA[Coffee SubTitle]]></text>
</staticText>
<staticText>
<reportElement x="172" y="94" width="383" height="42" uuid="8240065e-64b6-4170-b5d9-6341598e7b35"/>
<textElement textAlignment="Right">
<font size="10"/>
</textElement>
<text><![CDATA[Lorem ipsum dolor sit amet, consectetur adipiscing elit. Fusce auctor purus gravida arcu aliquam mattis. Donec et nulla libero, ut varius massa. Nulla sed turpis elit. Etiam aliquet mauris a ligula hendrerit in auctor leo lobortis.]]></text>
</staticText>
<image>
<reportElement x="633" y="-73" width="219" height="209" uuid="4aa6f4d1-27dc-4b34-9227-59e19ce2c959"/>
<imageExpression><![CDATA["coffee_stain.png"]]></imageExpression>
</image>
</band>
</title>
<pageHeader>
<band splitType="Stretch"/>
</pageHeader>
<columnHeader>
<band height="31" splitType="Stretch">
<property name="com.jaspersoft.studio.unit.height" value="pixel"/>
<frame>
<reportElement positionType="Float" mode="Opaque" x="0" y="0" width="1186" height="24" forecolor="#D0B48E" backcolor="#D1C097" uuid="3b66efe2-820d-4fb8-a4ef-a00e32db3e19"/>
<box>
<topPen lineWidth="1.0" lineStyle="Solid" lineColor="#080808"/>
<leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#080808"/>
<bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#080808"/>
<rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#080808"/>
</box>
<staticText>
<reportElement mode="Transparent" x="0" y="0" width="62" height="24" uuid="a3981b29-3346-4672-973c-5d9f3d83dad0">
<property name="com.jaspersoft.studio.unit.x" value="pixel"/>
</reportElement>
<textElement textAlignment="Center" verticalAlignment="Middle"/>
<text><![CDATA[id]]></text>
</staticText>
<staticText>
<reportElement x="62" y="0" width="100" height="24" uuid="d1469b31-1f61-4937-91c8-9ab49d1c35c8">
<property name="com.jaspersoft.studio.unit.height" value="pixel"/>
<property name="com.jaspersoft.studio.unit.y" value="pixel"/>
<property name="com.jaspersoft.studio.unit.x" value="pixel"/>
</reportElement>
<textElement textAlignment="Center" verticalAlignment="Middle">
<font size="10"/>
</textElement>
<text><![CDATA[Sender FirstName]]></text>
</staticText>
<staticText>
<reportElement x="162" y="0" width="100" height="24" uuid="b3ba4d19-9d50-40d8-b72b-dc6bbeaa36a9">
<property name="com.jaspersoft.studio.unit.y" value="pixel"/>
<property name="com.jaspersoft.studio.unit.height" value="pixel"/>
<property name="com.jaspersoft.studio.unit.x" value="pixel"/>
</reportElement>
<textElement textAlignment="Center" verticalAlignment="Middle"/>
<text><![CDATA[Sender Last Name]]></text>
</staticText>
<staticText>
<reportElement x="262" y="0" width="100" height="24" uuid="30c681b9-f1f5-4341-a9bd-020a238919b1">
<property name="com.jaspersoft.studio.unit.y" value="pixel"/>
<property name="com.jaspersoft.studio.unit.height" value="pixel"/>
<property name="com.jaspersoft.studio.unit.x" value="pixel"/>
</reportElement>
<textElement textAlignment="Center" verticalAlignment="Middle"/>
<text><![CDATA[Sender Address]]></text>
</staticText>
<staticText>
<reportElement x="362" y="0" width="100" height="24" uuid="6febcc07-e416-4bc0-aba2-195eab3b964d">
<property name="com.jaspersoft.studio.unit.y" value="pixel"/>
<property name="com.jaspersoft.studio.unit.height" value="pixel"/>
<property name="com.jaspersoft.studio.unit.x" value="pixel"/>
</reportElement>
<textElement textAlignment="Center" verticalAlignment="Middle"/>
<text><![CDATA[Sender Town]]></text>
</staticText>
<staticText>
<reportElement x="462" y="0" width="100" height="24" uuid="88170a84-2d54-4caf-bd8d-cb9b205555fb">
<property name="com.jaspersoft.studio.unit.height" value="pixel"/>
<property name="com.jaspersoft.studio.unit.x" value="pixel"/>
</reportElement>
<textElement textAlignment="Center" verticalAlignment="Middle"/>
<text><![CDATA[Sender Parish]]></text>
</staticText>
<staticText>
<reportElement x="562" y="0" width="100" height="24" uuid="ae1778b5-7c90-4133-ac77-92aa314dc64b">
<property name="com.jaspersoft.studio.unit.y" value="pixel"/>
<property name="com.jaspersoft.studio.unit.height" value="pixel"/>
<property name="com.jaspersoft.studio.unit.x" value="pixel"/>
</reportElement>
<textElement textAlignment="Center" verticalAlignment="Middle"/>
<text><![CDATA[Sender Country]]></text>
</staticText>
<staticText>
<reportElement x="662" y="0" width="100" height="24" uuid="f2fc7b22-8b59-47d5-9c16-2bd4f2281109">
<property name="com.jaspersoft.studio.unit.y" value="pixel"/>
<property name="com.jaspersoft.studio.unit.height" value="pixel"/>
<property name="com.jaspersoft.studio.unit.x" value="pixel"/>
</reportElement>
<textElement textAlignment="Center" verticalAlignment="Middle"/>
<text><![CDATA[Sender PostalCode]]></text>
</staticText>
<staticText>
<reportElement x="762" y="0" width="120" height="24" uuid="7d1f9dba-7244-44a1-bb28-6a36097de3c8">
<property name="com.jaspersoft.studio.unit.y" value="pixel"/>
<property name="com.jaspersoft.studio.unit.height" value="pixel"/>
<property name="com.jaspersoft.studio.unit.x" value="pixel"/>
</reportElement>
<textElement textAlignment="Center" verticalAlignment="Middle"/>
<text><![CDATA[Sender Organization]]></text>
</staticText>
<staticText>
<reportElement x="880" y="0" width="100" height="24" uuid="be5b818a-918c-4f9f-aac8-1a1f655682d0">
<property name="com.jaspersoft.studio.unit.y" value="pixel"/>
<property name="com.jaspersoft.studio.unit.height" value="pixel"/>
</reportElement>
<textElement textAlignment="Center" verticalAlignment="Middle"/>
<text><![CDATA[comments]]></text>
</staticText>
<staticText>
<reportElement x="980" y="0" width="100" height="24" uuid="357f4e5f-20c9-4f2d-bb31-a5770454d063">
<property name="com.jaspersoft.studio.unit.y" value="pixel"/>
<property name="com.jaspersoft.studio.unit.height" value="pixel"/>
</reportElement>
<textElement textAlignment="Center" verticalAlignment="Middle"/>
<text><![CDATA[priority]]></text>
</staticText>
<staticText>
<reportElement x="1080" y="0" width="100" height="24" uuid="4f4b32b4-4284-4052-8814-2f473d0c31be">
<property name="com.jaspersoft.studio.unit.y" value="pixel"/>
<property name="com.jaspersoft.studio.unit.height" value="pixel"/>
</reportElement>
<textElement textAlignment="Center" verticalAlignment="Middle"/>
<text><![CDATA[status]]></text>
</staticText>
</frame>
</band>
</columnHeader>
<detail>
<band height="60" splitType="Immediate">
<textField>
<reportElement style="Row" x="63" y="0" width="100" height="30" uuid="364252fd-3434-49bc-b8f9-e5fcd22351e7">
<property name="com.jaspersoft.studio.unit.y" value="pixel"/>
</reportElement>
<textElement textAlignment="Center" verticalAlignment="Middle"/>
<textFieldExpression><![CDATA[$F{sender_first_name}]]></textFieldExpression>
</textField>
<textField>
<reportElement style="Row" x="163" y="0" width="100" height="30" uuid="a817c625-ad3f-42f2-b2c3-0b2871c538b4">
<property name="com.jaspersoft.studio.unit.y" value="pixel"/>
</reportElement>
<textElement textAlignment="Center" verticalAlignment="Middle"/>
<textFieldExpression><![CDATA[$F{sender_last_name}]]></textFieldExpression>
</textField>
<textField>
<reportElement style="Row" x="263" y="0" width="100" height="30" uuid="0d86dbcd-5741-427d-8fce-ef0afc60fa97">
<property name="com.jaspersoft.studio.unit.y" value="pixel"/>
</reportElement>
<textElement textAlignment="Center" verticalAlignment="Middle"/>
<textFieldExpression><![CDATA[$F{senderaddress}]]></textFieldExpression>
</textField>
<textField>
<reportElement style="Row" x="363" y="0" width="100" height="30" uuid="5ec9aaa1-2cb6-47d0-8611-397e71a2d2a2">
<property name="com.jaspersoft.studio.unit.y" value="pixel"/>
<property name="com.jaspersoft.studio.unit.x" value="pixel"/>
</reportElement>
<textElement textAlignment="Center" verticalAlignment="Middle"/>
<textFieldExpression><![CDATA[$F{sendertown}]]></textFieldExpression>
</textField>
<textField>
<reportElement style="Row" x="463" y="0" width="100" height="30" uuid="4d801c7c-b772-4345-8413-3892b62358ff">
<property name="com.jaspersoft.studio.unit.y" value="pixel"/>
</reportElement>
<textElement textAlignment="Center" verticalAlignment="Middle"/>
<textFieldExpression><![CDATA[$F{senderparish}]]></textFieldExpression>
</textField>
<textField>
<reportElement style="Row" x="763" y="0" width="120" height="30" uuid="7a4cb0b9-b08a-4ecc-890c-041935826f00">
<property name="com.jaspersoft.studio.unit.y" value="pixel"/>
</reportElement>
<textElement textAlignment="Center" verticalAlignment="Middle"/>
<textFieldExpression><![CDATA[$F{sender_organization}]]></textFieldExpression>
</textField>
<textField>
<reportElement style="Row" x="883" y="0" width="100" height="30" uuid="4dd9b15b-f3eb-4a66-a81e-7fcf12855b0d">
<property name="com.jaspersoft.studio.unit.x" value="pixel"/>
</reportElement>
<textElement textAlignment="Center" verticalAlignment="Middle"/>
<textFieldExpression><![CDATA[$F{comments}]]></textFieldExpression>
</textField>
<textField>
<reportElement style="Row" x="983" y="0" width="100" height="30" uuid="ba4099d1-d7a7-4eed-9655-f879f46c675d">
<property name="com.jaspersoft.studio.unit.y" value="pixel"/>
</reportElement>
<textElement textAlignment="Center" verticalAlignment="Middle"/>
<textFieldExpression><![CDATA[$F{priority}]]></textFieldExpression>
</textField>
<textField>
<reportElement style="Row" x="1083" y="0" width="100" height="30" uuid="70b0c45b-2af1-45b6-8e2e-895466116713">
<property name="com.jaspersoft.studio.unit.y" value="pixel"/>
<property name="com.jaspersoft.studio.unit.x" value="pixel"/>
</reportElement>
<textElement textAlignment="Center" verticalAlignment="Middle"/>
<textFieldExpression><![CDATA[$F{status}]]></textFieldExpression>
</textField>
<textField>
<reportElement style="Row" x="0" y="0" width="63" height="30" uuid="89d926eb-0859-4fb1-90c7-73e013f01360"/>
<textElement textAlignment="Center" verticalAlignment="Middle"/>
<textFieldExpression><![CDATA[$F{master_id}]]></textFieldExpression>
</textField>
<textField>
<reportElement style="Row" x="563" y="0" width="100" height="30" uuid="ead45fa9-65b4-4cdb-9dcf-298ce5597716">
<property name="com.jaspersoft.studio.unit.y" value="pixel"/>
</reportElement>
<textElement textAlignment="Center" verticalAlignment="Middle"/>
<textFieldExpression><![CDATA[$F{sendercountry}]]></textFieldExpression>
</textField>
<textField>
<reportElement style="Row" x="663" y="0" width="100" height="30" uuid="12828fb6-3543-4c7d-a60a-5b09319c453b">
<property name="com.jaspersoft.studio.unit.y" value="pixel"/>
<property name="com.jaspersoft.studio.unit.x" value="pixel"/>
</reportElement>
<textElement textAlignment="Center" verticalAlignment="Middle"/>
<textFieldExpression><![CDATA[$F{senderpostalcode}]]></textFieldExpression>
</textField>
<subreport>
<reportElement x="131" y="42" width="200" height="18" uuid="7abbb62c-0007-48e2-9776-fcff37eeb02a"/>
<subreportParameter name="master_id">
<subreportParameterExpression><![CDATA[$F{master_id}]]></subreportParameterExpression>
</subreportParameter>
<connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
<subreportExpression><![CDATA["Coffee_Table_Based_SubReport.jasper"]]></subreportExpression>
</subreport>
</band>
</detail>
<columnFooter>
<band height="19"/>
</columnFooter>
<pageFooter>
<band height="25" splitType="Stretch">
<frame>
<reportElement mode="Opaque" x="0" y="1" width="1200" height="24" forecolor="#D0B48E" backcolor="#F2EBDF" uuid="5d8169bd-4a75-48c8-8a68-6d3ad5ba9402"/>
<textField evaluationTime="Report">
<reportElement style="Column header" x="1150" y="1" width="40" height="20" forecolor="#736343" uuid="e5e27efa-b599-499b-9ca3-848cb511cb7b"/>
<textElement verticalAlignment="Middle">
<font size="10" isBold="false"/>
</textElement>
<textFieldExpression><![CDATA[" " + $V{PAGE_NUMBER}]]></textFieldExpression>
</textField>
<textField>
<reportElement style="Column header" x="1060" y="2" width="80" height="20" forecolor="#736343" uuid="18cfe1ca-f7d6-48b0-9827-28578b42a5e0"/>
<textElement textAlignment="Right" verticalAlignment="Middle">
<font size="10" isBold="false"/>
</textElement>
<textFieldExpression><![CDATA["Page "+$V{PAGE_NUMBER}+" of"]]></textFieldExpression>
</textField>
<textField pattern="EEEEE dd MMMMM yyyy">
<reportElement style="Column header" x="22" y="1" width="197" height="20" forecolor="#736343" uuid="fbce24bb-3cb1-44a3-8eec-8c067ddbe5b5"/>
<textElement verticalAlignment="Middle">
<font size="10" isBold="false"/>
</textElement>
<textFieldExpression><![CDATA[new java.util.Date()]]></textFieldExpression>
</textField>
</frame>
</band>
</pageFooter>
<summary>
<band splitType="Stretch"/>
</summary>
</jasperReport>
Below is my subreport.
<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.2.2.final using JasperReports Library version 6.2.2 -->
<!-- 2017-02-13T17:04:44 -->
<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="Coffee_Table_Based_SubReport" pageWidth="595" pageHeight="842" whenNoDataType="AllSectionsNoDetail" columnWidth="535" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="868819c0-2e1e-457b-84c5-82067f527855">
<property name="com.jaspersoft.studio.data.sql.tables" value=""/>
<property name="com.jaspersoft.studio.data.defaultdataadapter" value="webinteractive_1"/>
<style name="Title" fontName="Times New Roman" fontSize="50" isBold="true" pdfFontName="Times-Bold"/>
<style name="SubTitle" forecolor="#736343" fontName="Arial" fontSize="18"/>
<style name="Column header" forecolor="#666666" fontName="Arial" fontSize="12" isBold="true"/>
<style name="Detail" backcolor="#F0EADF" fontName="Arial" fontSize="12">
<conditionalStyle>
<conditionExpression><![CDATA[$V{REPORT_COUNT}%2 == 0]]></conditionExpression>
<style backcolor="#E6DAC3"/>
</conditionalStyle>
</style>
<style name="Row" mode="Transparent"/>
<style name="Table">
<box>
<pen lineWidth="1.0" lineColor="#000000"/>
<topPen lineWidth="1.0" lineColor="#000000"/>
<leftPen lineWidth="1.0" lineColor="#000000"/>
<bottomPen lineWidth="1.0" lineColor="#000000"/>
<rightPen lineWidth="1.0" lineColor="#000000"/>
</box>
</style>
<style name="Table_TH" mode="Opaque" backcolor="#FFFFFF">
<box>
<pen lineWidth="0.5" lineColor="#000000"/>
<topPen lineWidth="0.5" lineColor="#000000"/>
<leftPen lineWidth="0.5" lineColor="#000000"/>
<bottomPen lineWidth="0.5" lineColor="#000000"/>
<rightPen lineWidth="0.5" lineColor="#000000"/>
</box>
</style>
<style name="Table_CH" mode="Opaque" forecolor="#B89F7D" backcolor="#F2EBDF">
<box>
<pen lineWidth="0.5" lineColor="#000000"/>
<topPen lineWidth="0.5" lineColor="#000000"/>
<leftPen lineWidth="0.5" lineColor="#000000"/>
<bottomPen lineWidth="0.5" lineColor="#000000"/>
<rightPen lineWidth="0.5" lineColor="#000000"/>
</box>
</style>
<style name="Table_TD" mode="Opaque" backcolor="#FFFFFF">
<box>
<pen lineWidth="0.5" lineColor="#000000"/>
<topPen lineWidth="0.5" lineColor="#000000"/>
<leftPen lineWidth="0.5" lineColor="#000000"/>
<bottomPen lineWidth="0.5" lineColor="#000000"/>
<rightPen lineWidth="0.5" lineColor="#000000"/>
</box>
<conditionalStyle>
<conditionExpression><![CDATA[$V{REPORT_COUNT}%2 == 0]]></conditionExpression>
<style mode="Transparent" backcolor="#E6DAC3"/>
</conditionalStyle>
</style>
<subDataset name="tableDataset" uuid="0722b386-dbda-4243-86e6-4656b3a81d54">
<property name="com.jaspersoft.studio.data.defaultdataadapter" value="webinteractive_1"/>
<property name="com.jaspersoft.studio.data.sql.tables" value=""/>
<parameter name="masterId" class="java.lang.Integer">
<parameterDescription><![CDATA[]]></parameterDescription>
<defaultValueExpression><![CDATA[1]]></defaultValueExpression>
</parameter>
<queryString>
<![CDATA[SELECT public.incoming_mail_detail.status,
public.incoming_mail_detail.file_id,
public.incoming_mail_detail.priority,
public.incoming_mail_detail.document_type,
public.incoming_mail_detail.document_id,
public.incoming_mail_detail.due_date,
public.incoming_mail_detail.master_id,
public.incoming_mail_detail.description,
public.incoming_mail_master.id AS masterId,
public.incoming_mail_master.created_on,
public.incoming_mail_master.created_by,
public.document_master.name AS documentName
FROM public.incoming_mail_detail,
public.incoming_mail_master,
public.address,
public.document_master,
public.master_document_type
WHERE
public.document_master.id = public.incoming_mail_detail.document_id
AND public.address.id = public.incoming_mail_master.sender_address_id
AND public.incoming_mail_detail.master_id = public.incoming_mail_master.id
AND public.document_master.id = public.incoming_mail_detail.document_id
AND public.master_document_type.id = public.incoming_mail_detail.document_type
AND public.incoming_mail_detail.master_id = $P{master_id}]]>
</queryString>
<field name="status" class="java.lang.String"/>
<field name="file_id" class="java.lang.Long"/>
<field name="priority" class="java.lang.String"/>
<field name="document_type" class="java.lang.Long"/>
<field name="document_id" class="java.lang.Long"/>
<field name="due_date" class="java.sql.Timestamp"/>
<field name="master_id" class="java.lang.Long"/>
<field name="description" class="java.lang.String"/>
<field name="masterid" class="java.lang.Long"/>
<field name="created_on" class="java.sql.Timestamp"/>
<field name="created_by" class="java.lang.String"/>
<field name="documentname" class="java.lang.String"/>
</subDataset>
<parameter name="masterId" class="java.lang.Integer" isForPrompting="false">
<defaultValueExpression><![CDATA[1]]></defaultValueExpression>
</parameter>
<parameter name="master_id" class="java.lang.Integer" isForPrompting="false">
<parameterDescription><![CDATA[]]></parameterDescription>
<defaultValueExpression><![CDATA[1]]></defaultValueExpression>
</parameter>
<queryString>
<![CDATA[]]>
</queryString>
<variable name="master_id" class="java.lang.Integer"/>
<summary>
<band height="200" splitType="Stretch">
<property name="local_mesure_unitheight" value="pixel"/>
<componentElement>
<reportElement stretchType="RelativeToBandHeight" x="0" y="0" width="555" height="80" isRemoveLineWhenBlank="true" isPrintWhenDetailOverflows="true" uuid="4d21cbcb-4f65-47a5-8795-12bc4b523b8d">
<property name="com.jaspersoft.studio.table.style.table_header" value="Table_TH"/>
<property name="com.jaspersoft.studio.table.style.column_header" value="Table_CH"/>
<property name="com.jaspersoft.studio.table.style.detail" value="Table_TD"/>
</reportElement>
<jr:table xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd">
<datasetRun subDataset="tableDataset" uuid="91b9ad2f-e018-4ecb-864a-8ae55cc51c50">
<connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
</datasetRun>
<jr:column width="100" uuid="2e09d707-9042-4a82-b0aa-efed0427f23b">
<property name="com.jaspersoft.studio.components.table.model.column.name" value="Column1"/>
<jr:columnHeader style="Table_CH" height="30" rowSpan="1">
<staticText>
<reportElement key="" style="SubTitle" mode="Opaque" x="0" y="0" width="100" height="30" forecolor="#000000" backcolor="#D1C097" uuid="cd40abfc-e1fa-4223-9a00-ea282d1214b4"/>
<box>
<topPen lineWidth="0.0" lineStyle="Solid" lineColor="#FFFFFF"/>
<leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#FFFFFF"/>
<bottomPen lineWidth="0.0" lineStyle="Solid" lineColor="#FFFFFF"/>
<rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#FFFFFF"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Middle">
<font fontName="Arial" size="10" isBold="true"/>
</textElement>
<text><![CDATA[Name]]></text>
</staticText>
</jr:columnHeader>
<jr:detailCell style="Table_TD" height="30">
<textField>
<reportElement style="Detail" mode="Opaque" x="0" y="0" width="100" height="30" uuid="9f360ee5-d553-457b-a8b2-b6b1f7d2d9d9"/>
<box>
<topPen lineColor="#FFFFFF"/>
<leftPen lineColor="#FFFFFF"/>
<bottomPen lineColor="#FFFFFF"/>
<rightPen lineColor="#FFFFFF"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Middle">
<font fontName="Arial" size="10"/>
</textElement>
<textFieldExpression><![CDATA[$F{documentname}]]></textFieldExpression>
</textField>
</jr:detailCell>
</jr:column>
<jr:column width="130" uuid="73355cdc-3bbb-421e-bbe1-8e45e61a301b">
<property name="com.jaspersoft.studio.components.table.model.column.name" value="Column2"/>
<jr:columnHeader style="Table_CH" height="30" rowSpan="1">
<staticText>
<reportElement key="" style="SubTitle" mode="Opaque" x="0" y="0" width="130" height="30" forecolor="#000000" backcolor="#D1C097" uuid="8048dea2-c35b-4d45-bdd2-e13b62c8d132"/>
<box>
<topPen lineWidth="1.0" lineStyle="Solid" lineColor="#FFFFFF"/>
<leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#FFFFFF"/>
<bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#FFFFFF"/>
<rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#FFFFFF"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Middle">
<font fontName="Arial" size="10" isBold="true"/>
</textElement>
<text><![CDATA[Description]]></text>
</staticText>
</jr:columnHeader>
<jr:detailCell style="Table_TD" height="30">
<textField>
<reportElement style="Detail" mode="Opaque" x="0" y="0" width="130" height="30" uuid="7bc1497b-adf4-40cf-91e6-13b1eb5d7a76"/>
<box>
<topPen lineColor="#FFFFFF"/>
<leftPen lineColor="#FFFFFF"/>
<bottomPen lineColor="#FFFFFF"/>
<rightPen lineColor="#FFFFFF"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Middle">
<font fontName="Arial" size="10"/>
</textElement>
<textFieldExpression><![CDATA[$F{description}]]></textFieldExpression>
</textField>
</jr:detailCell>
</jr:column>
<jr:column width="80" uuid="1445700f-bce5-4648-bbdc-311497602885">
<property name="com.jaspersoft.studio.components.table.model.column.name" value="Column3"/>
<jr:columnHeader style="Table_CH" height="30" rowSpan="1">
<staticText>
<reportElement key="" style="SubTitle" mode="Opaque" x="0" y="0" width="80" height="30" forecolor="#000000" backcolor="#D1C097" uuid="86547384-fc7f-46dd-a8e8-78f4b541a615"/>
<box>
<topPen lineColor="#FFFFFF"/>
<leftPen lineColor="#FFFFFF"/>
<bottomPen lineColor="#FFFFFF"/>
<rightPen lineColor="#FFFFFF"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Middle">
<font fontName="Arial" size="10" isBold="true"/>
</textElement>
<text><![CDATA[File Id]]></text>
</staticText>
</jr:columnHeader>
<jr:detailCell style="Table_TD" height="30">
<textField>
<reportElement mode="Opaque" x="0" y="0" width="80" height="30" backcolor="#F0EADF" uuid="cb4a7114-04b0-4f41-8cc6-cf6e8b0c8107"/>
<textElement textAlignment="Center" verticalAlignment="Middle"/>
<textFieldExpression><![CDATA[$F{file_id}]]></textFieldExpression>
</textField>
</jr:detailCell>
</jr:column>
<jr:column width="100" uuid="a5a60dee-b05a-4eed-9c01-a8daaa2d7f42">
<property name="com.jaspersoft.studio.components.table.model.column.name" value="Column4"/>
<jr:columnHeader style="Table_CH" height="30" rowSpan="1">
<staticText>
<reportElement key="" style="SubTitle" mode="Opaque" x="0" y="0" width="100" height="30" forecolor="#000000" backcolor="#D1C097" uuid="e936c75e-95f5-4d66-b75c-4de0e7530673"/>
<box>
<topPen lineColor="#FFFFFF"/>
<leftPen lineColor="#FFFFFF"/>
<bottomPen lineColor="#FFFFFF"/>
<rightPen lineColor="#FFFFFF"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Middle">
<font fontName="Arial" size="10" isBold="true"/>
</textElement>
<text><![CDATA[Document Type]]></text>
</staticText>
</jr:columnHeader>
<jr:detailCell style="Table_TD" height="30">
<textField>
<reportElement style="Detail" mode="Opaque" x="0" y="0" width="100" height="30" uuid="75b7e1f3-a814-47c0-a533-d70e24e70bcd"/>
<box>
<topPen lineColor="#FFFFFF"/>
<leftPen lineColor="#FFFFFF"/>
<bottomPen lineColor="#FFFFFF"/>
<rightPen lineColor="#FFFFFF"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Middle">
<font fontName="Arial" size="10"/>
</textElement>
<textFieldExpression><![CDATA[$F{document_type}]]></textFieldExpression>
</textField>
</jr:detailCell>
</jr:column>
<jr:column width="70" uuid="6cca732e-bf9c-4dd4-918e-48cfaf378914">
<property name="com.jaspersoft.studio.components.table.model.column.name" value="Column5"/>
<jr:columnHeader style="Table_CH" height="30" rowSpan="1">
<staticText>
<reportElement key="" style="SubTitle" mode="Opaque" x="0" y="0" width="70" height="30" forecolor="#000000" backcolor="#D1C097" uuid="1b095593-5ff3-49e7-8c46-cc7c9907f27b"/>
<box>
<topPen lineColor="#FFFFFF"/>
<leftPen lineColor="#FFFFFF"/>
<bottomPen lineColor="#FFFFFF"/>
<rightPen lineColor="#FFFFFF"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Middle">
<font fontName="Arial" size="10" isBold="true"/>
</textElement>
<text><![CDATA[Due Date]]></text>
</staticText>
</jr:columnHeader>
<jr:detailCell style="Table_TD" height="30">
<textField>
<reportElement style="Detail" mode="Opaque" x="0" y="0" width="70" height="30" uuid="77d3b2f1-194c-47b1-b5ba-1058fb7b2b65"/>
<box>
<topPen lineColor="#FFFFFF"/>
<leftPen lineColor="#FFFFFF"/>
<bottomPen lineColor="#FFFFFF"/>
<rightPen lineColor="#FFFFFF"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Middle">
<font fontName="Arial" size="10"/>
</textElement>
<textFieldExpression><![CDATA[$F{due_date}]]></textFieldExpression>
</textField>
</jr:detailCell>
</jr:column>
<jr:column width="75" uuid="83da246c-7995-453a-8194-2d1b077ed823">
<property name="com.jaspersoft.studio.components.table.model.column.name" value="Column6"/>
<jr:columnHeader style="Table_CH" height="30" rowSpan="1">
<staticText>
<reportElement key="" style="SubTitle" mode="Opaque" x="0" y="0" width="75" height="30" forecolor="#000000" backcolor="#D1C097" uuid="10cc9371-ba80-40f1-91fc-95e08a459d9f"/>
<box>
<topPen lineColor="#FFFFFF"/>
<leftPen lineColor="#FFFFFF"/>
<bottomPen lineColor="#FFFFFF"/>
<rightPen lineColor="#FFFFFF"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Middle">
<font fontName="Arial" size="10" isBold="true"/>
</textElement>
<text><![CDATA[Priority]]></text>
</staticText>
</jr:columnHeader>
<jr:detailCell style="Table_TD" height="30">
<textField>
<reportElement style="Detail" mode="Opaque" x="0" y="0" width="75" height="30" uuid="6e1dae21-349f-48d4-a6dc-30f1c51c8b5d"/>
<box>
<topPen lineColor="#FFFFFF"/>
<leftPen lineColor="#FFFFFF"/>
<bottomPen lineColor="#FFFFFF"/>
<rightPen lineColor="#FFFFFF"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Middle">
<font fontName="Arial" size="10"/>
</textElement>
<textFieldExpression><![CDATA[$F{priority}]]></textFieldExpression>
</textField>
</jr:detailCell>
</jr:column>
</jr:table>
</componentElement>
</band>
</summary>
</jasperReport>
thanks in advance.
3 Answers:
Posted on February 13, 2017 at 9:57pm
Thank you for your answer. But still m getting below error. In where condition I had used below statement
AND public.incoming_mail_detail.master_id = $P{masterId}
And it is not able to recognize the parameter. I defined paramter masterId in subreport and in masterreport assigned this parameter to $F{master_id} [field value]. If I remove above mentioned condition then it will run. Can you please tell me what I had done wrong here.
net.sf.jasperreports.engine.JRException: net.sf.jasperreports.engine.JRRuntimeException: net.sf.jasperreports.engine.JRException: Error executing SQL statement for: Coffee_Landscape_subReport.
at com.jaspersoft.studio.editor.preview.view.control.ReportControler.fillReport(ReportControler.java:530)
at com.jaspersoft.studio.editor.preview.view.control.ReportControler.access$20(ReportControler.java:505)
at com.jaspersoft.studio.editor.preview.view.control.ReportControler$5.run(ReportControler.java:386)
at org.eclipse.core.internal.jobs.Worker.run(Worker.java:55)
Caused by: net.sf.jasperreports.engine.JRRuntimeException: net.sf.jasperreports.engine.JRException: Error executing SQL statement for: Coffee_Landscape_subReport.
at net.sf.jasperreports.engine.fill.JRFillSubreport.prepare(JRFillSubreport.java:874)
at net.sf.jasperreports.engine.fill.JRFillElementContainer.prepareElements(JRFillElementContainer.java:536)
at net.sf.jasperreports.engine.fill.JRFillBand.fill(JRFillBand.java:411)
at net.sf.jasperreports.engine.fill.JRFillBand.fill(JRFillBand.java:386)
at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillColumnBand(JRVerticalFiller.java:2024)
at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillDetail(JRVerticalFiller.java:748)
at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReportStart(JRVerticalFiller.java:255)
at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReport(JRVerticalFiller.java:115)
at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:580)
at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFill.run(BaseFillHandle.java:123)
at java.lang.Thread.run(Thread.java:745)
Caused by: net.sf.jasperreports.engine.JRException: Error executing SQL statement for: Coffee_Landscape_subReport.
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:344)
at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1129)
at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:696)
at net.sf.jasperreports.engine.fill.BaseReportFiller.setParameters(BaseReportFiller.java:437)
at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:548)
at net.sf.jasperreports.engine.fill.BaseReportFiller.fill(BaseReportFiller.java:396)
at net.sf.jasperreports.engine.fill.JRFillSubreport.fillSubreport(JRFillSubreport.java:732)
at net.sf.jasperreports.engine.fill.JRSubreportRunnable.run(JRSubreportRunnable.java:59)
at net.sf.jasperreports.engine.fill.AbstractThreadSubreportRunner.run(AbstractThreadSubreportRunner.java:221)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
... 1 more
Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: bigint = character varying
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Position: 662
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:273)
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:310)
... 11 more
Posted on February 13, 2017 at 11:32pm
it isn't the same error, now you have another and very clear:
ERROR: operator does not exist: bigint = character varying
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
The field is bigint and parameter is char or backwards. Both have to have the same type.
Regards,
Mariano
