Passing parameter from main report to sub report in jaspersoft studio 6.2

chavda.charmi · February 13, 2017

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"?>

</conditionalStyle>

</style>

<box>

</box>

</style>

</parameter>

</parameter>

<![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>

</group>

</background>

<title>

<image>

</image>

</staticText>

</textElement>

</staticText>

</textElement>

</staticText>

<image>

</image>

</band>

</title>

</pageHeader>

<frame>

<box>

</box>

</reportElement>

</staticText>

</reportElement>

</textElement>

</staticText>

</reportElement>

</staticText>

</reportElement>

</staticText>

</reportElement>

</staticText>

</reportElement>

</staticText>

</reportElement>

</staticText>

</reportElement>

</staticText>

</reportElement>

</staticText>

</reportElement>

</staticText>

</reportElement>

</staticText>

</reportElement>

</staticText>

</frame>

</band>

</columnHeader>

</reportElement>

</textField>

</reportElement>

</textField>

</reportElement>

</textField>

</reportElement>

</textField>

</reportElement>

</textField>

</reportElement>

</textField>

</reportElement>

</textField>

</reportElement>

</textField>

</reportElement>

</textField>

</textField>

</reportElement>

</textField>

</reportElement>

</textField>

</subreportParameter>

</subreport>

</band>

</detail>

</columnFooter>

<frame>

</textElement>

</textField>

</textElement>

</textField>

</textElement>

</textField>

</frame>

</band>

</pageFooter>

</summary>

</jasperReport>

Below is my subreport.

<?xml version="1.0" encoding="UTF-8"?>

</conditionalStyle>

</style>

<box>

</box>

</style>

<box>

</box>

</style>

<box>

</box>

</style>

<box>

</box>

</conditionalStyle>

</style>

</parameter>

<![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>

</subDataset>

</parameter>

</parameter>

<![CDATA[]]>

</queryString>

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

<jr:column width="100" uuid="2e09d707-9042-4a82-b0aa-efed0427f23b">

<jr:columnHeader style="Table_CH" height="30" rowSpan="1">

<box>

</box>

</textElement>

</staticText>

</jr:columnHeader>

<jr:detailCell style="Table_TD" height="30">

<box>

</box>

</textElement>

</textField>

</jr:detailCell>

</jr:column>

<jr:column width="130" uuid="73355cdc-3bbb-421e-bbe1-8e45e61a301b">

<jr:columnHeader style="Table_CH" height="30" rowSpan="1">

<box>

</box>

</textElement>

</staticText>

</jr:columnHeader>

<jr:detailCell style="Table_TD" height="30">

<box>

</box>

</textElement>

</textField>

</jr:detailCell>

</jr:column>

<jr:column width="80" uuid="1445700f-bce5-4648-bbdc-311497602885">

<jr:columnHeader style="Table_CH" height="30" rowSpan="1">

<box>

</box>

</textElement>

</staticText>

</jr:columnHeader>

<jr:detailCell style="Table_TD" height="30">

</textField>

</jr:detailCell>

</jr:column>

<jr:column width="100" uuid="a5a60dee-b05a-4eed-9c01-a8daaa2d7f42">

<jr:columnHeader style="Table_CH" height="30" rowSpan="1">

<box>

</box>

</textElement>

</staticText>

</jr:columnHeader>

<jr:detailCell style="Table_TD" height="30">

<box>

</box>

</textElement>

</textField>

</jr:detailCell>

</jr:column>

<jr:column width="70" uuid="6cca732e-bf9c-4dd4-918e-48cfaf378914">

<jr:columnHeader style="Table_CH" height="30" rowSpan="1">

<box>

</box>

</textElement>

</staticText>

</jr:columnHeader>

<jr:detailCell style="Table_TD" height="30">

<box>

</box>

</textElement>

</textField>

</jr:detailCell>

</jr:column>

<jr:column width="75" uuid="83da246c-7995-453a-8194-2d1b077ed823">

<jr:columnHeader style="Table_CH" height="30" rowSpan="1">

<box>

</box>

</textElement>

</staticText>

</jr:columnHeader>

<jr:detailCell style="Table_TD" height="30">

<box>

</box>

</textElement>

</textField>

</jr:detailCell>

</jr:column>

</jr:table>

</componentElement>

</band>

</summary>

</jasperReport>

thanks in advance.

mlopez_1 · February 13, 2017

I think this is not a problem with parameters but with some resource in the Report Unit as says at:

-----> Resource not found

Review all the resources in the Report Unit to see which is missing.

Cheers,

Mariano

chavda.charmi · February 14, 2017

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

mlopez_1 · February 14, 2017

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

Sign In

Passing parameter from main report to sub report in jaspersoft studio 6.2

Recommended Posts

chavda.charmi

Link to comment

Share on other sites

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Popular Days

mlopez_1

Link to comment

Share on other sites

chavda.charmi

Link to comment

Share on other sites

mlopez_1

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Activity

Products

Explore