René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

Jasper - Multiple Queries (aka sub datasets)

First example

The additional query <subDataset> returns one record with one column whose constant value is sub report.
This value is displayed for each record of the driving query as port of the <jr:listContents> tag. 
<?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                    ="report name" 
  pageWidth               =        "595" 
  pageHeight              =        "845"
  columnWidth             =        "595" 
  leftMargin              =          "0" 
  rightMargin             =          "0" 
  topMargin               =          "0" 
  bottomMargin            =          "0"
  whenResourceMissingType ="Empty"
>

<!-- is attribute whenResourceMissingType new ???? -->


<subDataset name="Q2">
    <parameter name="MANDANT" class="java.lang.Integer">
      <defaultValueExpression><![CDATA[new Integer(1)]]></defaultValueExpression>
    </parameter>
    <queryString>
      <![CDATA[

SELECT   'sub report' sr_col_1
FROM      dual

]]>
    </queryString>
    <field name="SR_COL_1" class="java.lang.String"> <fieldDescription><![CDATA[]]></fieldDescription> </field>

</subDataset>

  <parameter name="MANDANT" class="java.lang.Integer"> <defaultValueExpression><![CDATA[new Integer(1)]]></defaultValueExpression> </parameter>
  <parameter name="DATE"    class="java.util.Date"> <defaultValueExpression><![CDATA[new SimpleDateFormat("dd-MMM-yy").parse("01-APR-09")]]></defaultValueExpression> </parameter>

<queryString>
    <![CDATA[

    select 1 col_1, 'foo' col_2 from dual union all
    select 2 col_2, 'bar' col_2 from dual

]]> 
  </queryString>

  <field name="COL_1" class="java.math.BigDecimal"><fieldDescription><![CDATA[]]></fieldDescription> </field>
  <field name="COL_2" class="java.lang.String">    <fieldDescription><![CDATA[]]></fieldDescription> </field>

  <background>  <band splitType="Stretch"/>            </background>
  <title>       <band height="0" splitType="Stretch"/> </title>
  <pageHeader>  <band height="0" splitType="Stretch"/> </pageHeader>
  <columnHeader><band height="0" splitType="Stretch"/> </columnHeader>


  <detail>
    <band height="30" splitType="Stretch"> 
      <textField> <reportElement x=  "0" y="0" width="90" height="13"/> <textElement/> <textFieldExpression class="java.math.BigDecimal"><![CDATA[$F{COL_1}]]></textFieldExpression> </textField>
      <textField> <reportElement x="100" y="0" width="90" height="13"/> <textElement/> <textFieldExpression class="java.lang.String"    ><![CDATA[$F{COL_2}]]></textFieldExpression> </textField>

      <componentElement>
        <reportElement x="200" y="0" width="90" height="24"/>
        <jr:list 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="Q2"/>

          <jr:listContents height="24">
            <textField>
              <reportElement x="0" y="0" width="100" height="20"/>
              <textElement/>
              <textFieldExpression class="java.lang.String"><![CDATA[$F{SR_COL_1}]]></textFieldExpression>
            </textField>
          </jr:listContents>
        </jr:list>
      </componentElement>

    
    </band>
  </detail>

  <columnFooter>  <band height="0" splitType="Stretch"/> </columnFooter>
  <pageFooter>    <band height="0" splitType="Stretch"/> </pageFooter>
  <summary>       <band height="0" splitType="Stretch"/> </summary>

</jasperReport>

Second example

Basically the same as the previous example except that the sub query returns two records. 
<?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                    ="report name" 
  pageWidth               =        "595" 
  pageHeight              =        "845"
  columnWidth             =        "595" 
  leftMargin              =          "0" 
  rightMargin             =          "0" 
  topMargin               =          "0" 
  bottomMargin            =          "0"
  whenResourceMissingType ="Empty"
>

<!-- Subreport returns two records -->


<subDataset name="Q2">
    <queryString>
      <![CDATA[

select   'sub report rec_1' sr_col_1 from dual union all
select   'sub report rec_2' sr_col_1 from dual

]]>
    </queryString>
    <field name="SR_COL_1" class="java.lang.String"> <fieldDescription><![CDATA[]]></fieldDescription> </field>

</subDataset>

<queryString>
    <![CDATA[

    select 1 col_1, 'foo' col_2 from dual union all
    select 2 col_1, 'bar' col_2 from dual

]]> 
  </queryString>

  <field name="COL_1" class="java.math.BigDecimal"><fieldDescription><![CDATA[]]></fieldDescription> </field>
  <field name="COL_2" class="java.lang.String">    <fieldDescription><![CDATA[]]></fieldDescription> </field>

  <background>  <band            splitType="Stretch"/> </background>
  <title>       <band height="0" splitType="Stretch"/> </title>
  <pageHeader>  <band height="0" splitType="Stretch"/> </pageHeader>
  <columnHeader><band height="0" splitType="Stretch"/> </columnHeader>

  <detail>
    <band height="30" splitType="Stretch"> 
      <textField> <reportElement x=  "0" y="0" width="90" height="13"/> <textElement/> <textFieldExpression class="java.math.BigDecimal"><![CDATA[$F{COL_1}]]></textFieldExpression> </textField>
      <textField> <reportElement x="100" y="0" width="90" height="13"/> <textElement/> <textFieldExpression class="java.lang.String"    ><![CDATA[$F{COL_2}]]></textFieldExpression> </textField>

      <componentElement>
        <reportElement x="200" y="0" width="90" height="24"/>
        <jr:list 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="Q2">
          </datasetRun>
          <jr:listContents height="24">
            <textField>
              <reportElement x="0" y="0" width="100" height="20"/>
              <textElement/>
              <textFieldExpression class="java.lang.String"><![CDATA[$F{SR_COL_1}]]></textFieldExpression>
            </textField>
          </jr:listContents>
        </jr:list>
      </componentElement>

    </band>
  </detail>

  <columnFooter>  <band height="0" splitType="Stretch"/> </columnFooter>
  <pageFooter>    <band height="0" splitType="Stretch"/> </pageFooter>
  <summary>       <band height="0" splitType="Stretch"/> </summary>

</jasperReport>

Third example

Passing parameters to sub datasets.
The driving query accepts a parameter named SOME_NUMBER and then prints five records. For each of these records, the sub dataset is invoked and the value of the second column of the driving query is passed. That sub dataset returns a text shich then is displayed «as third column»
This works because the sub dataset returns one record only. 
<?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                    ="report name" 
  pageWidth               =        "595" 
  pageHeight              =        "845"
  columnWidth             =        "595" 
  leftMargin              =          "0" 
  rightMargin             =          "0" 
  topMargin               =          "0" 
  bottomMargin            =          "0"
  whenResourceMissingType ="Empty"
>


<subDataset name="Q2">
    <parameter name="SR_PARAM" class="java.math.BigDecimal"/>

    <queryString>
      <![CDATA[

         select sr_col_value from (
           select 1 sr_col_key, 'one'   sr_col_value from dual union all
           select 2 sr_col_key, 'two'   sr_col_value from dual union all
           select 3 sr_col_key, 'three' sr_col_value from dual union all
           select 4 sr_col_key, 'four'  sr_col_value from dual union all
           select 5 sr_col_key, 'five'  sr_col_value from dual union all
           select 6 sr_col_key, 'six'   sr_col_value from dual union all
           select 7 sr_col_key, 'seven' sr_col_value from dual union all
           select 8 sr_col_key, 'eight' sr_col_value from dual union all
           select 9 sr_col_key, 'nine'  sr_col_value from dual union all
           select 0 sr_col_key, 'zero'  sr_col_value from dual 
         )
         where sr_col_key = $P{SR_PARAM}

      ]]>

    </queryString>
    <field name="SR_COL_VALUE" class="java.lang.String"> <fieldDescription><![CDATA[]]></fieldDescription> </field>

</subDataset>

  <parameter name="SOME_NUMBER" class="java.lang.Integer"/> 

<queryString>
    <![CDATA[
     
     select 1 * $P{SOME_NUMBER} col_1, mod(1*$P{SOME_NUMBER}, 10) col_2 from dual union all 
     select 2 * $P{SOME_NUMBER} col_1, mod(2*$P{SOME_NUMBER}, 10) col_2 from dual union all 
     select 3 * $P{SOME_NUMBER} col_1, mod(3*$P{SOME_NUMBER}, 10) col_2 from dual union all 
     select 4 * $P{SOME_NUMBER} col_1, mod(4*$P{SOME_NUMBER}, 10) col_2 from dual union all 
     select 5 * $P{SOME_NUMBER} col_1, mod(5*$P{SOME_NUMBER}, 10) col_2 from dual

]]> 
  </queryString>

  <field name="COL_1" class="java.math.BigDecimal"><fieldDescription><![CDATA[]]></fieldDescription> </field>
  <field name="COL_2" class="java.math.BigDecimal"><fieldDescription><![CDATA[]]></fieldDescription> </field>

  <background>  <band            splitType="Stretch"/> </background>
  <title>       <band height="0" splitType="Stretch"/> </title>
  <pageHeader>  <band height="0" splitType="Stretch"/> </pageHeader>
  <columnHeader><band height="0" splitType="Stretch"/> </columnHeader>

  <detail>
    <band height="30" splitType="Stretch"> 
      <textField> <reportElement x=  "0" y="0" width="90" height="13"/> <textElement/> <textFieldExpression class="java.math.BigDecimal"><![CDATA[$F{COL_1}]]></textFieldExpression> </textField>
      <textField> <reportElement x="100" y="0" width="90" height="13"/> <textElement/> <textFieldExpression class="java.math.BigDecimal"><![CDATA[$F{COL_2}]]></textFieldExpression> </textField>

      <componentElement>
        <reportElement x="200" y="0" width="90" height="24"/>
        <jr:list 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="Q2">
						<datasetParameter name="SR_PARAM"> <datasetParameterExpression><![CDATA[$F{COL_2}]]></datasetParameterExpression> </datasetParameter>
          </datasetRun>
          <jr:listContents height="24">
            <textField>
              <reportElement x="0" y="0" width="100" height="20"/>
              <textElement/> <textFieldExpression class="java.lang.String"><![CDATA[$F{SR_COL_VALUE}]]></textFieldExpression>
            </textField>
          </jr:listContents>
        </jr:list>
      </componentElement>

    </band>
  </detail>

  <columnFooter>  <band height="0" splitType="Stretch"/> </columnFooter>
  <pageFooter>    <band height="0" splitType="Stretch"/> </pageFooter>
  <summary>       <band height="0" splitType="Stretch"/> </summary>

</jasperReport>

Fourth example

No promising rewult. I wanted to demonstrate how to use groups within sub datasets. 
<?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="report name"
  pageWidth="595" 
  pageHeight="845"
  columnWidth="595"
  leftMargin="0"
  rightMargin="0" 
  topMargin="0"
  bottomMargin="0" 
  whenResourceMissingType="Empty">

	<subDataset name="Q2">
		<parameter name="P_ITEM" class="java.lang.String"/>
		<queryString>
			<![CDATA[select * from (
  select 'Apple'  item, 'Mary'  who, 2 val from dual union all
  select 'Apple'  item, 'Mary'  who, 3 val from dual union all
  select 'Apple'  item, 'Peter' who, 1 val from dual union all
  select 'Apple'  item, 'Peter' who, 6 val from dual union all
  select 'Apple'  item, 'Peter' who, 2 val from dual union all
  select 'Orange' item, 'Sue'   who, 1 val from dual union all
  select 'Orange' item, 'Sue'   who, 2 val from dual union all
  select 'Orange' item, 'Sue'   who, 3 val from dual union all
  select 'Orange' item, 'Jeff'  who, 0 val from dual union all
  select 'Orange' item, 'Jeff'  who, 4 val from dual union all
  select 'Orange' item, 'Jeff'  who, 9 val from dual
)
where item = $P{P_ITEM}
order by who, item]]>
		</queryString>
		<field name="ITEM" class="java.lang.String">
			<fieldDescription><![CDATA[]]></fieldDescription>
		</field>
		<field name="VAL" class="java.math.BigDecimal">
			<fieldDescription><![CDATA[]]></fieldDescription>
		</field>
		<field name="WHO" class="java.lang.String"/>
		<variable name="sum_val_per_who" class="java.math.BigDecimal" resetType="Group" resetGroup="gr_who" calculation="Sum">
			<variableExpression><![CDATA[$F{VAL}]]></variableExpression>
		</variable>
		<group name="gr_who">
			<groupExpression><![CDATA[$F{WHO} ]]></groupExpression>
		</group>
	</subDataset>
	<queryString>
		<![CDATA[select 10 col_1, 'Apple'  col_2 from dual union all
    select 20 col_1, 'Orange' col_2 from dual]]>
	</queryString>
	<field name="COL_1" class="java.math.BigDecimal">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="COL_2" class="java.lang.String">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<background>
		<band splitType="Stretch"/>
	</background>
	<title>
		<band splitType="Stretch"/>
	</title>
	<pageHeader>
		<band splitType="Stretch"/>
	</pageHeader>
	<columnHeader>
		<band splitType="Stretch"/>
	</columnHeader>
	<detail>
		<band height="101" splitType="Stretch">
			<textField>
				<reportElement x="0" y="0" width="90" height="13"/>
				<textElement/>
				<textFieldExpression class="java.math.BigDecimal"><![CDATA[$F{COL_1}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement x="100" y="0" width="90" height="13"/>
				<textElement/>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{COL_2}]]></textFieldExpression>
			</textField>
			<componentElement>
				<reportElement x="187" y="25" width="330" height="76"/>
				<jr:list 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="Q2">
						<datasetParameter name="P_ITEM">
							<datasetParameterExpression><![CDATA[$F{COL_2}]]></datasetParameterExpression>
						</datasetParameter>
					</datasetRun>
					<jr:listContents height="76">
						<textField>
							<reportElement x="0" y="0" width="80" height="20"/>
							<textElement/>
							<textFieldExpression class="java.lang.String"><![CDATA[$F{ITEM}]]></textFieldExpression>
						</textField>
						<textField>
							<reportElement x="123" y="21" width="100" height="20"/>
							<textElement/>
							<textFieldExpression class="java.math.BigDecimal"><![CDATA[$F{VAL}]]></textFieldExpression>
						</textField>
						<textField>
							<reportElement x="229" y="21" width="100" height="20"/>
							<textElement/>
							<textFieldExpression class="java.math.BigDecimal"><![CDATA[$V{sum_val_per_who}]]></textFieldExpression>
						</textField>
						<textField>
							<reportElement x="2" y="21" width="100" height="20"/>
							<textElement/>
							<textFieldExpression class="java.lang.String"><![CDATA[$F{WHO}]]></textFieldExpression>
						</textField>
					</jr:listContents>
				</jr:list>
			</componentElement>
		</band>
	</detail>




</jasperReport>
See also other Jasper examples.