How to show sum values in jasper reports 3d bar charts?

Question

I'm trying to create a 3d bar chart which groups by type and shows the sum of squarefeet values for each type. I couldn't get the bars to show the sum of squarefeets for each types per color. Instead it seems to show the value of last squarefeet when there are more than one squarefeet value for same type and color.

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.6.0.final using JasperReports Library version 6.6.0  -->
<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="barChart" pageWidth="842" pageHeight="595" orientation="Landscape" columnWidth="802" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="4f80af00-8345-4feb-bd0c-5b0b3b2b6232">
    <property name="com.jaspersoft.studio.data.sql.tables" value=""/>
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="hsqlCon"/>
    <queryString language="SQL">
        <![CDATA[select * from Item where id IN (select itemId from TempItem )]]>
    </queryString>
    <field name="COLOR" class="java.lang.String">
        <property name="com.jaspersoft.studio.field.label" value="COLOR"/>
        <property name="com.jaspersoft.studio.field.tree.path" value="ITEM"/>
    </field>
    <field name="SQUAREFEET" class="java.lang.Double">
        <property name="com.jaspersoft.studio.field.label" value="SQUAREFEET"/>
        <property name="com.jaspersoft.studio.field.tree.path" value="ITEM"/>
    </field>
    <field name="TYPE" class="java.lang.String">
        <property name="com.jaspersoft.studio.field.label" value="TYPE"/>
        <property name="com.jaspersoft.studio.field.tree.path" value="ITEM"/>
    </field>
    <group name="TYPE">
        <groupExpression><![CDATA[$F{TYPE}]]></groupExpression>
    </group>
    <background>
        <band splitType="Stretch"/>
    </background>
    <title>
        <band height="79" splitType="Stretch"/>
    </title>
    <pageHeader>
        <band height="35" splitType="Stretch"/>
    </pageHeader>
    <detail>
        <band height="257" splitType="Stretch">
            <bar3DChart>
                <chart evaluationTime="Report">
                    <reportElement x="139" y="0" width="581" height="257" uuid="f2ceb018-5078-4aeb-90df-125419604d39"/>
                    <chartTitle/>
                    <chartSubtitle/>
                    <chartLegend/>
                </chart>
                <categoryDataset>
                    <categorySeries>
                        <seriesExpression><![CDATA[$F{COLOR}]]></seriesExpression>
                        <categoryExpression><![CDATA[$F{TYPE}]]></categoryExpression>
                        <valueExpression><![CDATA[SUM($F{SQUAREFEET})]]></valueExpression>
                    </categorySeries>
                </categoryDataset>
                <bar3DPlot>
                    <plot/>
                    <itemLabel/>
                    <categoryAxisFormat>
                        <axisFormat labelColor="#000000" tickLabelColor="#000000" axisLineColor="#000000"/>
                    </categoryAxisFormat>
                    <valueAxisFormat>
                        <axisFormat labelColor="#000000" tickLabelColor="#000000" axisLineColor="#000000"/>
                    </valueAxisFormat>
                </bar3DPlot>
            </bar3DChart>
        </band>
    </detail>
    <pageFooter>
        <band height="54" splitType="Stretch"/>
    </pageFooter>
</jasperReport>

Answer 1

NOTICE: I would recommend that all the calculations are done in the sql query in your example it could be SELECT TYPE, COLOR, SUM(SQUAREFEET) AS SQUAREFEET FROM Item WHERE id IN (SELECT itemId FROM TempItem) GROUP BY TYPE, COLOR. This will give you a "flat" dataource and you do not need to add groups, variables etc in jasper-report also the retrieved data is less which will decrease execution time of report. The example below to create a chart with your current data using groups and variables presume that your data is ordered by TYPE and COLOR (grouping in jasper reports works only if you have ordered data)

Some key points/quick tips before the full example

Chart should go in summary band, detail band is executed for each record, instead we want 1 chart for all records.

Your group is both on TYPE and on COLOR (when one of these change you have a new group) so the grouping expression needs to be something like:

<group name="TYPE_COLOR">
   <groupExpression><![CDATA[$F{TYPE}+"_"+$F{COLOR}]]></groupExpression>
</group>

We need a variable that collect the value of our sum (reset when group change), that we will use in the category series value expression.

<variable name="SUM_SQUAREFEET" class="java.lang.Double" resetType="Group" resetGroup="TYPE_COLOR" calculation="Sum">
        <variableExpression><![CDATA[$F{SQUAREFEET}]]></variableExpression>
</variable>

and we need to tell the chart when to collect data (when to add data to chart)

<dataset incrementType="Group" incrementGroup="TYPE_COLOR"/>

Full jrxml

<?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="barChart" pageWidth="842" pageHeight="595" orientation="Landscape" columnWidth="802" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="4f80af00-8345-4feb-bd0c-5b0b3b2b6232">
    <queryString language="SQL">
    <![CDATA[select * from Item where id IN (select itemId from TempItem )]]>
    </queryString>
    <field name="TYPE" class="java.lang.String"/>
    <field name="COLOR" class="java.lang.String"/>
    <field name="SQUAREFEET" class="java.lang.Double"/>
    <variable name="SUM_SQUAREFEET" class="java.lang.Double" resetType="Group" resetGroup="TYPE_COLOR" calculation="Sum">
        <variableExpression><![CDATA[$F{SQUAREFEET}]]></variableExpression>
    </variable>
    <group name="TYPE_COLOR">
        <groupExpression><![CDATA[$F{TYPE}+"_"+$F{COLOR}]]></groupExpression>
    </group>
    <summary>
        <band height="267">
            <bar3DChart>
                <chart>
                    <reportElement x="90" y="10" width="581" height="257" uuid="bcbac117-a9ab-424c-ae81-6f68d1b01f0c"/>
                    <chartTitle/>
                    <chartSubtitle/>
                    <chartLegend/>
                </chart>
                <categoryDataset>
                    <dataset incrementType="Group" incrementGroup="TYPE_COLOR"/>
                    <categorySeries>
                        <seriesExpression><![CDATA[$F{COLOR}]]></seriesExpression>
                        <categoryExpression><![CDATA[$F{TYPE}]]></categoryExpression>
                        <valueExpression><![CDATA[$V{SUM_SQUAREFEET}]]></valueExpression>
                    </categorySeries>
                </categoryDataset>
                <bar3DPlot>
                    <plot/>
                    <itemLabel/>
                    <categoryAxisFormat>
                        <axisFormat labelColor="#000000" tickLabelColor="#000000" axisLineColor="#000000"/>
                    </categoryAxisFormat>
                    <valueAxisFormat>
                        <axisFormat labelColor="#000000" tickLabelColor="#000000" axisLineColor="#000000"/>
                    </valueAxisFormat>
                </bar3DPlot>
            </bar3DChart>
        </band>
    </summary>
</jasperReport>

Output