How to get string values to sum up as Big.Decimal when negative numbers contains parentheses?

Question

I have a report in which I need to sum up all values of a given column. The values were converted to char (string) because of the need for negative number to be viewed as "(123.45)" and zeros as "-" .

Because it is written in SQL I have tried casting the case when as decimal or numeric but it gives the error invalid number, so that the variables I created for summing decimals would work, right now those summing Variables are set for string and it wont do the job.

For example when previewing the report there is a column/field with values:

504.22
(179)
311.67
-
1,345.78

I'm not able to in the summary band to sum up these values, (179) is negative -179.

Answer 1

To parse a String that contains negative numbers in parenthesis you can for example use java.text.DecimalFormat with the pattern "#,##0.00;(#,##0.00)" however this can not parse "-" as 0.

You will need to handle this special case by using a ternary expression, hence if "-" return 0 otherwise parse (presuming the field of your String number is called number) .

$F{number}.equals("-")?0:new java.text.DecimalFormat("#,##0.00;(#,##0.00)").parse($F{number})

Now just push this into your variabile expression

<variable name="SumStringNumber" class="java.math.BigDecimal" calculation="Sum">
    <variableExpression><![CDATA[$F{number}.equals("-")?0:new java.text.DecimalFormat("#,##0.00;(#,##0.00)").parse($F{number})]]></variableExpression>
    <initialValueExpression><![CDATA[0]]></initialValueExpression>
</variable>

Full jrxml example

<?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="SumStringNumber" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="afa29477-d3db-4d2a-921d-fded6f024c27">
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="StringNumbers"/>
    <queryString language="csv">
        <![CDATA[]]>
    </queryString>
    <field name="number" class="java.lang.String"/>
    <variable name="SumStringNumber" class="java.math.BigDecimal" calculation="Sum">
        <variableExpression><![CDATA[$F{number}.equals("-")?0:new java.text.DecimalFormat("#,##0.00;(#,##0.00)").parse($F{number})]]></variableExpression>
        <initialValueExpression><![CDATA[0]]></initialValueExpression>
    </variable>
    <detail>
        <band height="20" splitType="Stretch">
            <property name="com.jaspersoft.studio.unit.height" value="px"/>
            <textField>
                <reportElement x="0" y="0" width="100" height="20" uuid="472295d5-70de-43d6-b03f-df0452614a39">
                    <property name="com.jaspersoft.studio.unit.height" value="px"/>
                </reportElement>
                <textElement textAlignment="Right" verticalAlignment="Middle"/>
                <textFieldExpression><![CDATA[$F{number}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="100" y="0" width="100" height="20" uuid="20854419-7c55-43bd-86bf-b9dc98c5d51d"/>
                <textElement textAlignment="Right" verticalAlignment="Middle"/>
                <textFieldExpression><![CDATA[$F{number}.equals("-")?0:new java.text.DecimalFormat("#,##0.00;(#,##0.00)").parse($F{number})]]></textFieldExpression>
            </textField>
        </band>
    </detail>
    <summary>
        <band height="20" splitType="Stretch">
            <property name="com.jaspersoft.studio.unit.height" value="px"/>
            <textField pattern="#,##0.###;(#,##0.###)">
                <reportElement x="100" y="0" width="100" height="20" uuid="9851d330-1be1-423d-9d01-1a9972ce6a3b">
                    <property name="com.jaspersoft.studio.unit.height" value="px"/>
                </reportElement>
                <textElement textAlignment="Right" verticalAlignment="Middle">
                    <font isBold="true"/>
                </textElement>
                <textFieldExpression><![CDATA[$V{SumStringNumber}]]></textFieldExpression>
            </textField>
            <line>
                <reportElement x="0" y="0" width="200" height="1" uuid="2089772d-98f5-4906-b026-69211dfd0e99"/>
            </line>
        </band>
    </summary>
</jasperReport>

Output (with my arbitrary data)

In general, it's better to pass data in correct format to jasper report, hence in your case it should be passed as Number not as a String and then do the formatting in jasper-report (see example above how pattern is applied to the total sum). This will ensure correct data type when you export for example to excel etc.