Jump to content

How do I use aggregate results in a calculation?


mike_64

Recommended Posts

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.

JasperInventory.jpg.061559541219b68cf5d750d8a254c061.jpg

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.

JasperInventory2.jpg.9f50f3e640a935387a39d656e315d8cd.jpg

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]

 

Link to comment
Share on other sites

  • Replies 3
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

Posted Images

  • 2 weeks later...

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...