How to use a parameter from the main data set in a second dataset

0

Hello

Using JasperSoft Studio

 

I am try to create a report in Jasper Soft were I need 2 data sets. 

In the main dataset I have created 2 parameters start_data and end_data. This make the report first to prompt for a date. I am using this as $P{start_date} and $P{end_date} in my main query. This is working fine! 

Now I have added a second data set (no subreport)  with another query I need to make a chart. I would like to user the same $P{start_date} and $P{end_date} in the query as parameter but somehow this does not work. 

When compiling the report It states "query parameter not found" 

 

I simply added the same start_data and end_data in the second dataset parameter field. This triggers no error but the output is empty. Anyone an idea how I can make the second query use parameters from the main data set?

CaPsLoCkEr's picture
Joined: Oct 9 2017 - 5:35am
Last seen: 1 year 6 months ago

4 Answers:

0

If you are using a subreport or table element, Click on the Element and Navigate to Properties pane -> Dataset Options

In the dataset options, Parameters button should have these mapping options. 

Check this sample jrxml that I have created

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.4.0.final using JasperReports Library version 6.4.1  -->
<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="dataset" pageWidth="842" pageHeight="595" orientation="Landscape" columnWidth="802" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="41ad4074-cb41-4a31-9d0a-7429b1f4304f">
    <property name="com.jaspersoft.studio.data.sql.tables" value=""/>
    <style name="Table_TH" 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>
    <style name="Table_CH" mode="Opaque" backcolor="#BFE1FF">
        <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>
    </style>
    <subDataset name="SubDataset" uuid="28c1570d-9e49-44f1-ba39-75a28824b5d2">
        <property name="com.jaspersoft.studio.data.sql.tables" value=""/>
        <property name="com.jaspersoft.studio.data.defaultdataadapter" value="PRODSUP"/>
        <parameter name="start_date" class="java.sql.Date"/>
        <parameter name="end_date" class="java.sql.Date"/>
        <queryString language="SQL">
            <![CDATA[select * from dual]]>
        </queryString>
        <field name="DUMMY" class="java.lang.String"/>
    </subDataset>
    <parameter name="start_date" class="java.sql.Date"/>
    <parameter name="end_date" class="java.sql.Date"/>
    <queryString>
        <![CDATA[select * from dual]]>
    </queryString>
    <field name="DUMMY" class="java.lang.String"/>
    <background>
        <band splitType="Stretch"/>
    </background>
    <columnHeader>
        <band height="20" splitType="Stretch">
            <staticText>
                <reportElement x="0" y="0" width="80" height="20" uuid="a73fc6e4-9651-435d-8b29-9f09ed26e177"/>
                <text><![CDATA[start_date]]></text>
            </staticText>
            <staticText>
                <reportElement x="80" y="0" width="100" height="20" uuid="5c6c670f-062d-40e9-a799-70b38822b9ba"/>
                <text><![CDATA[end_date]]></text>
            </staticText>
        </band>
    </columnHeader>
    <detail>
        <band height="19" splitType="Stretch">
            <textField pattern="MM/dd/yyyy">
                <reportElement x="0" y="0" width="80" height="19" uuid="d1173bd3-c4ac-4c64-828e-7f496cdcc33e">
                    <property name="com.jaspersoft.studio.unit.width" value="pixel"/>
                </reportElement>
                <textFieldExpression><![CDATA[$P{start_date}]]></textFieldExpression>
            </textField>
            <textField pattern="MM/dd/yyyy">
                <reportElement x="80" y="0" width="100" height="19" uuid="e413e867-b220-43d7-a124-039bffc39cc5"/>
                <textFieldExpression><![CDATA[$P{end_date}]]></textFieldExpression>
            </textField>
        </band>
    </detail>
    <summary>
        <band height="60" splitType="Stretch">
            <componentElement>
                <reportElement x="0" y="20" width="200" height="40" uuid="a7b41590-80d0-4153-8c4a-2ca50e253a9c">
                    <property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.VerticalRowLayout"/>
                    <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="SubDataset" uuid="d6ff833e-fc16-4ebe-b4eb-414167f26146">
                        <datasetParameter name="start_date">
                            <datasetParameterExpression><![CDATA[$P{start_date}]]></datasetParameterExpression>
                        </datasetParameter>
                        <datasetParameter name="end_date">
                            <datasetParameterExpression><![CDATA[$P{end_date}]]></datasetParameterExpression>
                        </datasetParameter>
                        <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
                    </datasetRun>
                    <jr:column width="100" uuid="83c0f516-06ae-49d3-9a94-6c186059493f">
                        <property name="com.jaspersoft.studio.components.table.model.column.name" value="Column1"/>
                        <property name="com.jaspersoft.studio.unit.height" value="pixel"/>
                        <jr:columnHeader style="Table_CH" height="20" rowSpan="1">
                            <property name="com.jaspersoft.studio.unit.height" value="px"/>
                            <staticText>
                                <reportElement x="0" y="0" width="100" height="20" uuid="89208895-8a53-411e-83a7-fbc538b59156"/>
                                <text><![CDATA[start_date]]></text>
                            </staticText>
                        </jr:columnHeader>
                        <jr:detailCell style="Table_TD" height="20">
                            <property name="com.jaspersoft.studio.unit.height" value="px"/>
                            <textField pattern="MM/dd/yyyy">
                                <reportElement x="0" y="0" width="100" height="20" uuid="dc074a58-46d0-4869-8659-3cfbaa74f38a"/>
                                <textFieldExpression><![CDATA[$P{start_date}]]></textFieldExpression>
                            </textField>
                        </jr:detailCell>
                    </jr:column>
                    <jr:column width="100" uuid="1fee413f-54e7-473e-9cfa-58f517c6fa0a">
                        <property name="com.jaspersoft.studio.components.table.model.column.name" value="Column2"/>
                        <jr:columnHeader style="Table_CH" height="20" rowSpan="1">
                            <staticText>
                                <reportElement x="0" y="0" width="100" height="20" uuid="afc24d09-e545-4896-ae94-9647708029f4"/>
                                <text><![CDATA[end_date]]></text>
                            </staticText>
                        </jr:columnHeader>
                        <jr:detailCell style="Table_TD" height="20">
                            <textField pattern="MM/dd/yyyy">
                                <reportElement x="0" y="0" width="100" height="20" uuid="57448548-15d5-4b6c-872b-1d06347fcf9b"/>
                                <textFieldExpression><![CDATA[$P{end_date}]]></textFieldExpression>
                            </textField>
                        </jr:detailCell>
                    </jr:column>
                </jr:table>
            </componentElement>
            <staticText>
                <reportElement x="0" y="0" width="100" height="19" uuid="e41bfa2f-35af-4884-8218-3a78f95175ac"/>
                <text><![CDATA[from SubDataset]]></text>
            </staticText>
        </band>
    </summary>
</jasperReport>

reportdev's picture
1340
Joined: Oct 12 2015 - 12:05pm
Last seen: 1 week 4 days ago
0

Hello

thanks for your answer. But i am not using a sub report. I just added a new data set. I followed this tutorial: https://community.jaspersoft.com/wiki/creating-charts-and-datasets-jaspe...

 

I also need to make a chart, but like I said in the OP the data in the chart needs to be using the same date parameters in the main report. 

CaPsLoCkEr's picture
Joined: Oct 9 2017 - 5:35am
Last seen: 1 year 6 months ago
2

even for the charts, you should have Dataset Options. 

when you drag the chart element into a band, the chart options configuration should allow you to configure the dataset options.

 

reportdev's picture
1340
Joined: Oct 12 2015 - 12:05pm
Last seen: 1 week 4 days ago
0

I managed to find it :-) 

 

thanks for the answers :-) 

CaPsLoCkEr's picture
Joined: Oct 9 2017 - 5:35am
Last seen: 1 year 6 months ago
Feedback