SUM variable with condition

[igna_95]

Hi all,

I'm doing a report and I'm having troubles when I want to sum my cost. I have a variable that sums my cost for each row, but when the project code is repeated it should not sum. I want to sum the cost one time per project. The total Cost should be: 15.000 €.

I've something like this right now.

I hope someone can help me,

Thank you.

Project Name	Project Code	Cost	Employee
Danone	123	10.000 €	Mike
Danone	123	10.000 €	John
MGS	333	5.000€	Mike
	Total Cost:	25.000€

 

[reportdev]

Check this jrxml. In this jrxml, I have created a group based on the concatenation of $F{PROJECT_NAME}+$F{PROJECT_CODE}+$F{COST}.

In the sum variable, the increment type is above mentioned group and the reset type is report.

<?xml version="1.0" encoding="UTF-8"?><!-- Created with Jaspersoft Studio version 6.2.0.final using JasperReports Library version 6.2.0  --><!-- 2017-02-28T08:43:25 --><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="Blank_A4_Landscape_5" pageWidth="842" pageHeight="595" orientation="Landscape" columnWidth="802" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" isFloatColumnFooter="true" uuid="f7856b0a-f70e-4fd0-a91e-bd24a4fc6807">    <property name="com.jaspersoft.studio.data.sql.tables" value=""/>    <queryString>        <![CDATA[select 'Danone' as project_name, 123 as project_code, 10.00 as cost, 'Mike' as Employee from dual unionselect 'Danone' as project_name, 123 as project_code, 10.00 as cost, 'John' as Employee from dual unionselect 'MGS' as project_name, 333 as project_code, 5.00 as cost, 'Mike' as Employee from dual]]>    </queryString>    <field name="PROJECT_NAME" class="java.lang.String"/>    <field name="PROJECT_CODE" class="java.math.BigDecimal"/>    <field name="COST" class="java.math.BigDecimal"/>    <field name="EMPLOYEE" class="java.lang.String"/>    <variable name="COST1" class="java.math.BigDecimal" incrementType="Group" incrementGroup="diff" calculation="Sum">        <variableExpression><![CDATA[$F{COST}]]></variableExpression>    </variable>    <group name="diff">        <groupExpression><![CDATA[$F{PROJECT_NAME}+$F{PROJECT_CODE}+$F{COST}]]></groupExpression>    </group>    <background>        <band splitType="Stretch"/>    </background>    <columnHeader>        <band height="20" splitType="Stretch">            <staticText>                <reportElement x="0" y="0" width="80" height="20" uuid="ab4ddad1-77fe-450a-b840-34d89233ca83"/>                <textElement>                    <font size="9" isBold="true"/>                </textElement>                <text><![CDATA[PROJECT_NAME]]></text>            </staticText>            <staticText>                <reportElement x="80" y="0" width="100" height="20" uuid="b5537f05-32b3-4481-8804-56a61d36b113"/>                <textElement>                    <font size="9" isBold="true"/>                </textElement>                <text><![CDATA[PROJECT_CODE]]></text>            </staticText>            <staticText>                <reportElement x="180" y="0" width="100" height="20" uuid="5dd6237c-3d55-4e8f-8e56-0d3be2d19b14"/>                <textElement>                    <font size="9" isBold="true"/>                </textElement>                <text><![CDATA[COST]]></text>            </staticText>            <staticText>                <reportElement x="280" y="0" width="100" height="20" uuid="06c73570-296e-4c42-82eb-44c4bf762881"/>                <textElement>                    <font size="9" isBold="true"/>                </textElement>                <text><![CDATA[EMPLOYEE]]></text>            </staticText>        </band>    </columnHeader>    <detail>        <band height="20" splitType="Stretch">            <textField>                <reportElement x="0" y="0" width="80" height="20" uuid="24ebb0a9-02b2-4479-8e66-1375112065a5"/>                <textElement>                    <font size="9"/>                </textElement>                <textFieldExpression><![CDATA[$F{PROJECT_NAME}]]></textFieldExpression>            </textField>            <textField>                <reportElement x="80" y="0" width="100" height="20" uuid="68b6ab95-4e1e-45e5-a8df-23a1ea92d5c8"/>                <textElement>                    <font size="9"/>                </textElement>                <textFieldExpression><![CDATA[$F{PROJECT_CODE}]]></textFieldExpression>            </textField>            <textField>                <reportElement x="180" y="0" width="100" height="20" uuid="1ce21830-7dde-4224-8305-62871896707d"/>                <textElement>                    <font size="9"/>                </textElement>                <textFieldExpression><![CDATA[$F{COST}]]></textFieldExpression>            </textField>            <textField>                <reportElement x="280" y="0" width="100" height="20" uuid="2b0d8bf7-23a7-4cc2-966b-197812c82038"/>                <textElement>                    <font size="9"/>                </textElement>                <textFieldExpression><![CDATA[$F{EMPLOYEE}]]></textFieldExpression>            </textField>        </band>    </detail>    <columnFooter>        <band height="20">            <textField>                <reportElement x="180" y="0" width="100" height="20" uuid="e35862f1-3305-4f52-9e37-5048deffb717"/>                <textElement>                    <font isBold="true" isUnderline="true"/>                </textElement>                <textFieldExpression><![CDATA[$V{COST1}]]></textFieldExpression>            </textField>        </band>    </columnFooter></jasperReport>[/code]