jasper-reports
Jasper Reports: using aggregate results in a calculation
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.
Here is the report:
EDIT:
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>
Solution
This was apparently a glitch. I threw out the jrxml file and started from scratch. The results under the new jrxml were correct when I used cost1 and netFootage1.
Although the .jrxml files were identical, the first version gave me garbage results, while the second version gave me the correct information.
- jasperreports: vertical arrangement in jr:list doesn't work and results in overlapping elements
- Jasper Reports throws Exception when running JAR but working PERFECTLY in IntelliJ IDE
- Filling a Jasper Report but getting nulls
- The subreport repeats itself in Detail band
- JasperReport: How to get date from the "oracle.sql.TIMESTAMP' field?
- jrxml uuid attribute not allowed with JasperReports 6.21.0
- cvc-complex-type.3.2.2 attribute 'uuid' is not allowed to appear in element 'jasperreport'
- Jasper soft report expression to get comma separated string from object array parameter
- Extra blank page at the end of the report JasperReports
- How to display in Jasper BLOB Image?
- How to get date six month before current date in single line in Java?
- Jasper Report : Errors were encountered when compiling report expressions class file On server
- How to use date range type parameter as normal java.util.date in jasper
- JsonQLDataSource cannot be resolved to a type
- How to print page number to other language in Jasper Report by Jaspersoft Studio?
- jasper text field getting truncated
- How to show column at Crosstab even the data is absent
- Pass Fields to Subreports in Jasper Reports
- How to add table of content in Jasper report
- Grouping in Jasper Reports
- Jasper Reports Show "Page X of Y" using a single text field
- JasperReports (6.20.1) dependency not satisfied in Java project : Failure to find com.github.librepdf:openpdf:jar:1.3.30.jaspersoft.1
- How can I use parameter for lineColor in JasperReports?
- Jasper Reports Excel Output Problems
- Jasper Reports can't locate "package net.sf.jasperreports.engine does not exist" while running jar but everything working fine in IDE
- Enable Jasper Studio Webservice plugin
- Jaspersoft studio adjust position of fields relative to height of other fields
- How to add DynamicJasper report to Jasper Report as sub-report Dynamically
- Jasper Reports: JRBeanCollectionDataSource cannot be resolved to a type
- How to export to multiple sheets in excel from jasper report