How do I use aggregate results in a calculation?

[mike_64]

I'm creating a summary report in Java using Jasper Reports where information on logs (trees) and their prices is displayed. I want to show the price per thousand board feet for each species. The calculation for this is: cost * 1000 / netFootage. The data is supplied as a list of JavaBeans.

Problem: the resulting price/MBF is not correct, as demonstrated by doing the math using the cost and netFootage shown below.

The only way around this that I've been able to come up with is performing the summary calculations outside of Jasper Reports, and that's a headache I'd rather not suffer if I don't have to.

If I change from using the fields cost and netFootage to using the variables cost1 and netFootage1, I get this result, which is different... but still wrong.

This is the full inventory.jrxml file:

<?xml version="1.0" encoding="UTF-8"?><!-- Created with Jaspersoft Studio version 6.3.1.final using JasperReports Library version 6.3.1  --><!-- 2017-01-04T19:33:33 --><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="ScaleSummary" pageWidth="612" pageHeight="792" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="099eaac8-9789-4d24-96ed-6a0a1a8abbbe">    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="One Empty Record"/>    <parameter name="reportDate" class="java.lang.String" isForPrompting="false"/>    <queryString>        <![CDATA[]]>    </queryString>    <field name="species" class="java.lang.String"/>    <field name="netFootage" class="java.math.BigDecimal"/>    <field name="overheadCost" class="java.math.BigDecimal"/>    <field name="cost" class="java.math.BigDecimal"/>    <variable name="netFootage1" class="java.math.BigDecimal" resetType="Group" resetGroup="Species" calculation="Sum">        <variableExpression><![CDATA[$F{netFootage}]]></variableExpression>    </variable>    <variable name="species1" class="java.lang.Integer" resetType="Group" resetGroup="Species" calculation="Count">        <variableExpression><![CDATA[$F{species}]]></variableExpression>    </variable>    <variable name="priceRate1" class="java.math.BigDecimal" resetType="Group" resetGroup="Species">        <variableExpression><![CDATA[$F{cost}.multiply(new BigDecimal(1000)).divide( $F{netFootage} )]]></variableExpression>    </variable>    <variable name="cost1" class="java.math.BigDecimal" resetType="Group" resetGroup="Species" calculation="Sum">        <variableExpression><![CDATA[$F{cost}]]></variableExpression>    </variable>    <variable name="overhead1" class="java.math.BigDecimal" resetType="Group" resetGroup="Species" calculation="Sum">        <variableExpression><![CDATA[$F{overheadCost}]]></variableExpression>    </variable>    <variable name="lineTotal1" class="java.math.BigDecimal" resetType="Group" resetGroup="Species">        <variableExpression><![CDATA[$V{cost1}.add( $V{overhead1} )]]></variableExpression>    </variable>    <variable name="species2" class="java.lang.Integer" calculation="Count">        <variableExpression><![CDATA[$F{species}]]></variableExpression>    </variable>    <variable name="netFootage2" class="java.math.BigDecimal" calculation="Sum">        <variableExpression><![CDATA[$F{netFootage}]]></variableExpression>    </variable>    <variable name="priceRate2" class="java.math.BigDecimal">        <variableExpression><![CDATA[$F{cost}.multiply(new BigDecimal(1000)).divide( $F{netFootage} )]]></variableExpression>    </variable>    <variable name="overheadCost1" class="java.math.BigDecimal" calculation="Sum">        <variableExpression><![CDATA[$F{overheadCost}]]></variableExpression>    </variable>    <variable name="cost2" class="java.math.BigDecimal" calculation="Sum">        <variableExpression><![CDATA[$F{cost}]]></variableExpression>    </variable>    <variable name="lineTotal2" class="java.math.BigDecimal" calculation="Sum">        <variableExpression><![CDATA[$V{cost1}.add( $V{overhead1} )]]></variableExpression>    </variable>    <group name="Species">        <groupExpression><![CDATA[$F{species}]]></groupExpression>        <groupFooter>            <band height="15">                <textField isBlankWhenNull="true">                    <reportElement x="0" y="0" width="100" height="15" printWhenGroupChanges="Species" uuid="1123bb45-40fd-4fc9-a5f1-2e976d1a3f99">                        <property name="com.jaspersoft.studio.unit.height" value="pixel"/>                    </reportElement>                    <textFieldExpression><![CDATA[$F{species}]]></textFieldExpression>                </textField>                <textField>                    <reportElement x="190" y="-1" width="70" height="15" uuid="b655bef4-dcf0-4e3a-818d-8383020f3e8c"/>                    <textElement textAlignment="Right"/>                    <textFieldExpression><![CDATA[$V{netFootage1}]]></textFieldExpression>                </textField>                <textField>                    <reportElement x="110" y="0" width="80" height="15" printWhenGroupChanges="Species" uuid="db90f3b9-aca4-4ab5-97b0-48c9dfda815f"/>                    <textElement textAlignment="Right"/>                    <textFieldExpression><![CDATA[$V{species1}]]></textFieldExpression>                </textField>                <textField evaluationTime="Group" evaluationGroup="Species" pattern="¤#,##0.000;¤-#,##0.000" isBlankWhenNull="true">                    <reportElement x="260" y="0" width="80" height="15" printWhenGroupChanges="Species" uuid="8188363f-3984-4b11-bfcf-c99cf65c8a61">                        <property name="com.jaspersoft.studio.unit.height" value="pixel"/>                    </reportElement>                    <textElement textAlignment="Right"/>                    <textFieldExpression><![CDATA[$V{priceRate1}]]></textFieldExpression>                </textField>                <textField evaluationTime="Group" evaluationGroup="Species" pattern="¤#,##0.##;¤-#,##0.##">                    <reportElement x="340" y="0" width="80" height="15" uuid="8a707e2f-3e22-46d7-a833-f95a3538541c">                        <property name="com.jaspersoft.studio.unit.y" value="pixel"/>                        <property name="com.jaspersoft.studio.unit.height" value="pixel"/>                    </reportElement>                    <textElement textAlignment="Right"/>                    <textFieldExpression><![CDATA[$V{cost1}]]></textFieldExpression>                </textField>                <textField pattern="¤#,##0.00;¤-#,##0.00">                    <reportElement x="420" y="0" width="80" height="15" uuid="a345cd4e-32b5-4bd6-bb89-d3064bbe4042">                        <property name="com.jaspersoft.studio.unit.height" value="pixel"/>                    </reportElement>                    <textElement textAlignment="Right"/>                    <textFieldExpression><![CDATA[$V{overhead1}]]></textFieldExpression>                </textField>                <textField pattern="¤#,##0.00;¤-#,##0.00">                    <reportElement x="500" y="0" width="70" height="15" uuid="5679a2ee-43dd-4e52-9915-0da8b2788cfc">                        <property name="com.jaspersoft.studio.unit.height" value="pixel"/>                    </reportElement>                    <textElement textAlignment="Right"/>                    <textFieldExpression><![CDATA[$V{lineTotal1}]]></textFieldExpression>                </textField>            </band>        </groupFooter>    </group>    <background>        <band splitType="Stretch"/>    </background>    <title>        <band height="95" splitType="Stretch">            <image hAlign="Center">                <reportElement x="0" y="-2" width="580" height="50" uuid="93bfff74-d6ee-4b94-a5b8-4db39f4515d5"/>                <imageExpression><![CDATA["images/title.png"]]></imageExpression>            </image>            <staticText>                <reportElement x="0" y="50" width="190" height="20" uuid="614fca9d-ef2c-4930-aa6b-0b4be9e88bea"/>                <textElement>                    <font size="14" isBold="true"/>                </textElement>                <text><![CDATA[Current Inventory Report]]></text>            </staticText>            <textField isBlankWhenNull="true">                <reportElement x="445" y="80" width="125" height="15" uuid="2f423025-4158-42ad-b5bc-29aec3d63ee2"/>                <textFieldExpression><![CDATA[$P{reportDate}]]></textFieldExpression>            </textField>            <staticText>                <reportElement x="354" y="80" width="91" height="15" uuid="2dbec0c0-05cc-46f5-bf36-d92609e5c152"/>                <textElement textAlignment="Right"/>                <text><![CDATA[Report Date:]]></text>            </staticText>        </band>    </title>    <columnHeader>        <band height="21" splitType="Stretch">            <line>                <reportElement x="0" y="15" width="570" height="1" isRemoveLineWhenBlank="true" uuid="a18f566c-5972-43f5-8754-00a46cdf262f"/>                <graphicElement>                    <pen lineWidth="2.0"/>                </graphicElement>            </line>            <staticText>                <reportElement x="0" y="1" width="100" height="20" uuid="d839c8d1-ff31-4aa0-a0ea-d0d57780f919"/>                <text><![CDATA[species]]></text>            </staticText>            <staticText>                <reportElement x="500" y="0" width="70" height="20" uuid="b6a482c2-52e7-4e87-b4e1-cd59a605964d"/>                <textElement textAlignment="Right"/>                <text><![CDATA[Total Cost]]></text>            </staticText>            <staticText>                <reportElement x="420" y="0" width="80" height="20" uuid="dcdaf6e6-d07a-4d59-a702-2ca5ee0e2667"/>                <textElement textAlignment="Right"/>                <text><![CDATA[Overhead Cost]]></text>            </staticText>            <staticText>                <reportElement x="340" y="0" width="80" height="20" uuid="0064bc76-4f28-4fcd-a6a4-a0021e37c329"/>                <textElement textAlignment="Right"/>                <text><![CDATA[Cost]]></text>            </staticText>            <staticText>                <reportElement x="260" y="0" width="80" height="20" uuid="a5e40833-70bc-4afa-a16e-33f7bd9225a5"/>                <textElement textAlignment="Right"/>                <text><![CDATA[$/MBF]]></text>            </staticText>            <staticText>                <reportElement x="180" y="0" width="80" height="20" uuid="80325097-ad9c-4cb7-8f1d-cd28e508b85b"/>                <textElement textAlignment="Right"/>                <text><![CDATA[Net BFt]]></text>            </staticText>            <staticText>                <reportElement x="110" y="0" width="80" height="20" uuid="b4bc60d0-fac0-48c0-8db3-f7cae8abbbf0"/>                <textElement textAlignment="Right"/>                <text><![CDATA[# of Logs]]></text>            </staticText>        </band>    </columnHeader>    <pageFooter>        <band height="27" splitType="Stretch"/>    </pageFooter>    <summary>        <band height="81" splitType="Stretch">            <line>                <reportElement x="0" y="0" width="570" height="1" isRemoveLineWhenBlank="true" uuid="2815f185-79f9-4c63-9c3e-a26d889e290c">                    <property name="com.jaspersoft.studio.unit.y" value="pixel"/>                </reportElement>                <graphicElement>                    <pen lineWidth="2.0"/>                </graphicElement>            </line>            <staticText>                <reportElement x="0" y="0" width="100" height="15" uuid="31141427-83b2-49f4-8edc-d238fdcfb701"/>                <textElement textAlignment="Right">                    <font isBold="true"/>                </textElement>                <text><![CDATA[Totals: ]]></text>            </staticText>            <textField>                <reportElement x="110" y="0" width="80" height="15" uuid="baa57730-fef0-4194-a2a0-1fb67428615b">                    <property name="com.jaspersoft.studio.unit.height" value="pixel"/>                </reportElement>                <textElement textAlignment="Right"/>                <textFieldExpression><![CDATA[$V{species2}]]></textFieldExpression>            </textField>            <textField>                <reportElement x="190" y="0" width="70" height="15" uuid="e9fc1f24-c4d8-430e-a9ed-15874c450c15"/>                <textElement textAlignment="Right"/>                <textFieldExpression><![CDATA[$V{netFootage2}]]></textFieldExpression>            </textField>            <textField pattern="¤#,##0.000;¤-#,##0.000">                <reportElement x="260" y="0" width="80" height="15" uuid="673065c9-010a-40ac-bae0-3286df02b659"/>                <textElement textAlignment="Right"/>                <textFieldExpression><![CDATA[$V{priceRate2}]]></textFieldExpression>            </textField>            <textField pattern="¤#,##0.00;¤-#,##0.00">                <reportElement x="420" y="0" width="80" height="15" uuid="a96091a6-d61a-4624-aa5b-49fe00cdde75"/>                <textElement textAlignment="Right"/>                <textFieldExpression><![CDATA[$V{overheadCost1}]]></textFieldExpression>            </textField>            <textField pattern="¤#,##0.##;¤-#,##0.##">                <reportElement x="340" y="0" width="80" height="15" uuid="1530dda8-714f-48e8-886c-66e1c0122c65"/>                <textElement textAlignment="Right"/>                <textFieldExpression><![CDATA[$V{cost2}]]></textFieldExpression>            </textField>            <textField pattern="¤#,##0.00;¤-#,##0.00">                <reportElement x="500" y="0" width="70" height="15" uuid="57e3165b-60cc-4e08-bf71-9b9b3681219c"/>                <textElement textAlignment="Right"/>                <textFieldExpression><![CDATA[$V{lineTotal2}]]></textFieldExpression>            </textField>        </band>    </summary></jasperReport>[/code]

 

[hozawa]

No sure but shouldn't you be using $V instead of $F in your aggregation result calculation?

$V{cost2}.multiply(new BigDecimal(1000)).divide( $V{netFootage2} )

[mike_64]

@hozawa: As noted above, I've tried this and the results have been unsatisfactory.

[mike_64]

I had hoped this was just a glitch, as I fixed it by deleting the existing calculations and recreating them. HOWEVER, I find I'm now facing a similar problem.

See this posting.