Round values without affecting total sum

[sara.machado]

 

I am trying throught subdataset's to arrive to the total rounded without affecting the calculation, but no success so far. Additionally, I find a little bit odd not find any solution so far regarding this issue.

Basically I do not want to use the Pattern option since it is influencing the rounded total values (as you can see in the example above). What I pretend is to guarantee the 2 digits after decimal point but that being only about final layout and not imposing to any calculation. Since I am new to ireport functionality can someone give me any special advise? thanks in advance

[ernst_2]

Hi sara.machado,

I am not sure why the pattern is not working for you.

This is my test file and it seems to work correctly:

<?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="report2" language="groovy" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" isFloatColumnFooter="true" uuid="7d1d2338-32cf-402d-bcde-e20af78120ae">    <property name="ireport.zoom" value="1.0"/>    <property name="ireport.x" value="0"/>    <property name="ireport.y" value="0"/>    <queryString>        <![CDATA[sELECT 0.5344 AS SEMUNION ALLSELECT 508.564UNION ALLSELECT 508.495899999999UNION ALLSELECT 69.8347]]>    </queryString>    <field name="sem" class="java.math.BigDecimal"/>    <variable name="SUM_SEM" class="java.math.BigDecimal" calculation="Sum">        <variableExpression><![CDATA[$F{sem}]]></variableExpression>    </variable>    <columnHeader>        <band height="20" splitType="Stretch">            <staticText>                <reportElement x="0" y="0" width="100" height="20" uuid="470afba4-c01c-46ef-ac75-b960c8f55bd0"/>                <text><![CDATA[sem]]></text>            </staticText>        </band>    </columnHeader>    <detail>        <band height="20" splitType="Stretch">            <textField pattern="#,##0.00">                <reportElement x="0" y="0" width="100" height="20" uuid="eb7dd5f0-922f-4b4c-8c32-93bbb01571dc"/>                <textFieldExpression><![CDATA[$F{sem}]]></textFieldExpression>            </textField>        </band>    </detail>    <columnFooter>        <band height="20" splitType="Stretch">            <textField pattern="#,##0.00">                <reportElement x="0" y="0" width="100" height="20" uuid="f7409b2d-0a68-49e2-a786-f4cb1761c2e0"/>                <textElement>                    <font isBold="true"/>                </textElement>                <textFieldExpression><![CDATA[$V{SUM_SEM}]]></textFieldExpression>            </textField>        </band>    </columnFooter></jasperReport>[/code]

 

[sara.machado]

hello Ernst, thanks for your input. However I may not been able to properly explained the issue, let me try to put it another way. Back to my example for the sake of explanation:

Basically what I need is to do the sum exactly like column 1 in order to give a more precise total amount and afterwards print all the values of the column 1 with just 2 decimal points like the layout of column 2. If I use pattern (that I tried before) it will calculate and print like column 2 which is not ok if you want to minimize the differences when we put it with other amounts. Do you think is possible to calculate and print different stuff in ireport? thanks

	sum without round (column 1)	sum with round (column 2)
	0.5344	0.53
	508.564	508.56
	508.495899999999	508.50	This value is the same but the second column is rounded
	69.8347	69.83
TOTAL	1,087.43	1087.42	Thus I find a small difference that I do not want to exist

[ernst_2]

Are you sure, that you use the sum function right? (you have to sum the unrounded values)

Because this is my result of the report and I think this is what you want.

[sara.machado]

Yes, it is exactly what I need, the sum of unrounded values. I use the pattern before so what is the difference between what you have done? I even manipulatted my XML to look similar at yours and even so I do not drive to the same solution. can you address your solution in steps please? when I use your entire XML I get an error:

Error loading the report template:
 org.xml.sax.SAXParseException; lineNumber: 2; columnNumber: 479; cvc-complex-type.3.2.2: Attribute 'uuid' is not allowed to appear in element 'jasperReport'.

[ernst_2]

 

It's magic :D

No, it's very simple.

You only have to pass your field into the expression of a variable and use the sum calculation type.

SQL:

SELECT 0.5344 AS SEMUNION ALLSELECT 508.564UNION ALLSELECT 508.495899999999UNION ALLSELECT 69.8347[/code]

Variable:

name: SUM_SEM

class: java.math.BigDecimal

calculation: Sum

reset type: report

increment type: none

variable expression: $F{sem}

TextField:

expression: $V{SUM_SEM}

pattern: #,##0.00

[sara.machado]

That it is already what I had (your magic formula :P)....and the issue is here. My output is different because all the values are being calculated but not rounded.

How did you managed to put rounded values on your output without messing the calculation? if I put pattern it happens the problem that originated this question..I am not understanding the difference indeed. Thanks

sum without round

0.5344

508.564

509.10

508.495899999999

69.8347

1,087.43

[ernst_2]

I use also a TextField like this:

TextField:

expression: $F{sem}

pattern: #,##0.00

 

The pattern does not change the value itself, it’s only for printing.

And I don't know how you manage it, to calculate with the rounded value of the TextField.

[sara.machado]

yeah pure magic indeed!! :P Now it is working and I do not see how it didn't before. Thanks so much Ernst 

Cheers

[christoph.schubert]

It's been a while, but I searched for such an issue and your hints lead me to this:

My measures wre setup automatically as "java.lang.Integer" and the total was calculated for this one. The the rounding does not work properly.

Just changed the type to "java.math.BigDecimal" (btw.: which is also the type of the field from the query, so I don't know why the measure was created as Integer...)

Then the rounding in the total field was working fine with the pattern #,##0.00  (or even #,##0.### if you want to eliminate decimal numbers at all)

Regards

Chris