Using field values for Parameter values, pass to Sub-Report

1

I can not find this in the forum or the iReport Guide.

Is it possible to pass values of fields in a report to the parameters in a sub-report? I thought I read this is the proper method to set the value of a parameter to be passed the the SQL of a sub-report.

The main report prompts the user for some data. This value is loaded into a parameter and used in the SQL statement. A record is returned, printed, value from a field is inserted into another parameter and the sub-report is called to print the detail records.

 

Any suggestion on best practice???

Thanks.

evalentine's picture
Joined: May 7 2009 - 1:37pm
Last seen: 2 years 11 months ago

1 Answer:

2

Absolutely possible.  I've pasted jrxml for a report I've done recently that includes a subreport for every detail record.  If you click on the subreport and look at the Properties pane, scroll down to the bottom and you see "Parameters" and "Return Values".  This particular report passes the field values $F{PERSON_SERIAL} and $F{BRANCH_SERIAL} into the subreport parameters PersonSerial and BranchSerial (in the subreport, they're referred to as $P{PersonSerial}and ${BranchSerial}, but you don't have to put the names inside $P{} in this dialog, since they're assumed to be parameter names.

Likewise, in the "Return Values" section, the left side of the dialog shows any subreport variable names that you want returned to the main report and the right side of the dialog shows the "Destination Variable"--that is, the variable name of the variable in the main report where you want those values to end up.  You are also prompted for a "Calculation Type", which should just be "Nothing" if you're just trying to pass a single value back to the Main reoprt intact.  For variables in your main report that get their information from a subreport like this, set the Calculation to "System", and your "Reset type" will vary, depending on what you're using the variable for.  In my case, I actually return one variable from the subreport to three different variables in the main report called SubTotNegBal (a variable for that detail), SubTotByBranch (a variable to subtotal by group), and GrandTotal (total for the entire main report).  It doesn't seem to work to just define the second and third variables in terms of being a sum of the first one when you use subreports, so this is the recommended method.  For that second and third variable in the main report, instead of setting the calculation type to "Nothing" in the "Return Values" section of the subreport, I set it to "Sum".

Hope that helps.

Carl

Code:
<?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 <a href="http://jasperreports.sourceforge.net/xsd/jasperreport.xsd"" target="_blank">http://jasperreports.sourceforge.net/xsd/jasperreport.xsd"</a> name="NegSharesByBranch" language="groovy" pageWidth="612" pageHeight="792" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20">
	<property name="ireport.zoom" value="1.0"/>
	<property name="ireport.x" value="0"/>
	<property name="ireport.y" value="0"/>
	<subDataset name="PostingDateDataset">
		<queryString>
			<![CDATA[SELECT
  ENV.POSTING_DATE
FROM
  CORE.ENV AS ENV]]>
		</queryString>
		<field name="POSTING_DATE" class="java.sql.Date">
			<fieldDescription><![CDATA[]]></fieldDescription>
		</field>
	</subDataset>
	<parameter name="SUBREPORT_DIR" class="java.lang.String" isForPrompting="false">
		<defaultValueExpression><![CDATA["C:\\\\Users\\\\cbarlow\\\\Documents\\\\JasperSoft\\\\"]]></defaultValueExpression>
	</parameter>
	<parameter name="IS_RUNNING_LOCAL" class="java.lang.Boolean">
		<defaultValueExpression><![CDATA[false]]></defaultValueExpression>
	</parameter>
	<parameter name="SUB_REP_LOCATION" class="java.lang.String" isForPrompting="false">
		<defaultValueExpression><![CDATA[$P{IS_RUNNING_LOCAL}.booleanValue() ? "NegSharesPerPerson.jasper" : "repo:NegSharesPerPerson.jrxml"]]></defaultValueExpression>
	</parameter>
	<queryString>
		<![CDATA[SELECT DISTINCT
  BRANCH.SERIAL AS BRANCH_SERIAL,
  BRANCH.DESCRIPTION AS BRANCH_DESCRIPTION,
  PERSON.SERIAL AS PERSON_SERIAL,
  PERSON.LAST_NAME || ',' || PERSON.FIRST_NAME AS PERSON_LAST_FIRST
FROM
  CORE.SHARE AS SHARE INNER JOIN
  CORE.ACCOUNT AS ACCOUNT ON
  SHARE.PARENT_SERIAL=ACCOUNT.SERIAL INNER JOIN
  CORE.PERSON AS PERSON ON
  ACCOUNT.PRIMARY_PERSON_SERIAL=PERSON.SERIAL INNER JOIN
  CORE.BRANCH AS BRANCH ON
  SHARE.BRANCH_SERIAL=BRANCH.SERIAL
WHERE
  SHARE.BALANCE<0
ORDER BY
  BRANCH.DESCRIPTION, PERSON_LAST_FIRST]]>
	</queryString>
	<field name="BRANCH_SERIAL" class="java.lang.Long">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="BRANCH_DESCRIPTION" class="java.lang.String">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="PERSON_SERIAL" class="java.lang.Long">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="PERSON_LAST_FIRST" class="java.lang.String">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<variable name="SubTotNegBal" class="java.math.BigDecimal" resetType="None" calculation="System">
		<initialValueExpression><![CDATA[0]]></initialValueExpression>
	</variable>
	<variable name="SubTotByBranch" class="java.math.BigDecimal" resetType="Group" resetGroup="BranchGroup" calculation="System">
		<variableExpression><![CDATA[]]></variableExpression>
		<initialValueExpression><![CDATA[0]]></initialValueExpression>
	</variable>
	<variable name="GrandTotal" class="java.math.BigDecimal" calculation="System">
		<initialValueExpression><![CDATA[0]]></initialValueExpression>
	</variable>
	<group name="BranchGroup" isStartNewPage="true" isReprintHeaderOnEachPage="true">
		<groupExpression><![CDATA[$F{BRANCH_SERIAL}]]></groupExpression>
		<groupHeader>
			<band height="16">
				<textField isStretchWithOverflow="true" bookmarkLevel="1">
					<reportElement x="0" y="0" width="327" height="16"/>
					<textElement>
						<font isBold="true"/>
					</textElement>
					<textFieldExpression class="java.lang.String"><![CDATA[$F{BRANCH_DESCRIPTION}+(($V{BranchGroup_COUNT}>0) ? " (Continued):" : ":")]]></textFieldExpression>
					<anchorNameExpression><![CDATA[$F{BRANCH_DESCRIPTION}]]></anchorNameExpression>
				</textField>
			</band>
		</groupHeader>
		<groupFooter>
			<band height="19">
				<textField>
					<reportElement x="0" y="0" width="285" height="19"/>
					<textElement textAlignment="Right">
						<font isBold="true"/>
					</textElement>
					<textFieldExpression class="java.lang.String"><![CDATA["SubTotal Neg Balance for Branch "+$F{BRANCH_DESCRIPTION}+":"]]></textFieldExpression>
				</textField>
				<textField pattern="¤ #,##0.00">
					<reportElement x="285" y="0" width="100" height="19"/>
					<textElement textAlignment="Right">
						<font isBold="true"/>
					</textElement>
					<textFieldExpression class="java.math.BigDecimal"><![CDATA[$V{SubTotByBranch}]]></textFieldExpression>
				</textField>
			</band>
		</groupFooter>
	</group>
	<background>
		<band splitType="Stretch"/>
	</background>
	<title>
		<band height="22" splitType="Stretch">
			<staticText>
				<reportElement x="0" y="0" width="327" height="22"/>
				<textElement>
					<font size="14" isBold="true"/>
				</textElement>
				<text><![CDATA[Negative Shares by Branch]]></text>
			</staticText>
			<componentElement>
				<reportElement x="466" y="0" width="106" height="22"/>
				<jr:list xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components <a href="http://jasperreports.sourceforge.net/xsd/components.xsd"" target="_blank">http://jasperreports.sourceforge.net/xsd/components.xsd"</a> printOrder="Vertical">
					<datasetRun subDataset="PostingDateDataset">
						<connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
					</datasetRun>
					<jr:listContents height="22" width="106">
						<textField pattern="MM/dd/yyyy">
							<reportElement x="0" y="0" width="106" height="20"/>
							<textElement textAlignment="Right"/>
							<textFieldExpression class="java.util.Date"><![CDATA[$F{POSTING_DATE}]]></textFieldExpression>
						</textField>
					</jr:listContents>
				</jr:list>
			</componentElement>
		</band>
	</title>
	<columnHeader>
		<band height="15" splitType="Stretch"/>
	</columnHeader>
	<detail>
		<band height="19" splitType="Stretch">
			<subreport>
				<reportElement x="0" y="0" width="572" height="19"/>
				<subreportParameter name="PersonSerial">
					<subreportParameterExpression><![CDATA[$F{PERSON_SERIAL}]]></subreportParameterExpression>
				</subreportParameter>
				<subreportParameter name="BranchSerial">
					<subreportParameterExpression><![CDATA[$F{BRANCH_SERIAL}]]></subreportParameterExpression>
				</subreportParameter>
				<connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
				<returnValue subreportVariable="SubTotNegBal" toVariable="SubTotNegBal"/>
				<returnValue subreportVariable="SubTotNegBal" toVariable="SubTotByBranch" calculation="Sum"/>
				<returnValue subreportVariable="SubTotNegBal" toVariable="GrandTotal" calculation="Sum"/>
				<subreportExpression class="java.lang.String"><![CDATA[$P{SUB_REP_LOCATION}]]></subreportExpression>
			</subreport>
		</band>
	</detail>
	<pageFooter>
		<band height="16" splitType="Stretch">
			<textField>
				<reportElement x="452" y="0" width="80" height="16"/>
				<textElement textAlignment="Right"/>
				<textFieldExpression class="java.lang.String"><![CDATA["Page "+$V{PAGE_NUMBER}+" of"]]></textFieldExpression>
			</textField>
			<textField evaluationTime="Report">
				<reportElement x="532" y="0" width="40" height="16"/>
				<textElement/>
				<textFieldExpression class="java.lang.String"><![CDATA[" " + $V{PAGE_NUMBER}]]></textFieldExpression>
			</textField>
		</band>
	</pageFooter>
	<summary>
		<band height="20">
			<staticText>
				<reportElement x="0" y="0" width="285" height="20"/>
				<textElement textAlignment="Right">
					<font isBold="true"/>
				</textElement>
				<text><![CDATA[Grand Total Negative Balance:]]></text>
			</staticText>
			<textField pattern="¤ #,##0.00">
				<reportElement x="285" y="0" width="100" height="20"/>
				<textElement textAlignment="Right">
					<font isBold="true"/>
				</textElement>
				<textFieldExpression class="java.math.BigDecimal"><![CDATA[$V{GrandTotal}]]></textFieldExpression>
			</textField>
		</band>
	</summary>
</jasperReport>
</td></tr></tbody></table>
cbarlow3's picture
1387
Joined: Mar 4 2010 - 8:59am
Last seen: 4 days 22 hours ago
Feedback
randomness