JasperStudio Issue with Sum of Sub Report Return Values

[lori.stopka]

I am utterly stumped.  I apologize for the very long explanation but I could think of no easy way to describe what is happening. 

I have several sub-reports in my main report.  Each sub-report has a return value all of which I want to summarize and print in a group footer on the master report.  The variables in the master and sub reports are defined as java.math.BigDecimal.  The variables in the sub-reports all have an Initial Value Expression is set to BigDecimal.valueOf(0).  I set the initial value thinking the sub report would return 0 rather than null when no data was found.    I am successfully able to return the values and display the variables individually in the group footer.  Zeroes display for the sub-reports that don't return any data.  

I created a new variable in my main report and used the SUM function to add them together (see example below).   IF all the variables return values from the sub-reports, the SUM works perfectly, even when one of the values returned is negative.  If any of the sub reports return 0 (eg null), things go south from there. 

A=10, B=10, C=10, D=10, E=10, F=-10     all add up correctly to 40.  Note the negative value for F

BUT

A=10, B=10, C=0, D=10, E=-10, F=0     my sum is 30.  A, B and D are added together and the negative value in E is ignored.

Note I do need to “reverse the sign” on two of the incoming values but that does not seem to affect the end result.

SUM (($V{A} == null ? BigDecimal.valueOf(0) : $V{A}),

    ($V{B} == null ? BigDecimal.valueOf(0) : ($V{B}*-1)),

    ($V{C} == null ? BigDecimal.valueOf(0) : $V{C}),

    ($V{D} == null ? BigDecimal.valueOf(0) : ($V{D}*-1)),

    ($V{E} == null ? BigDecimal.valueOf(0) : $V{E}),

    ($V{F} == null ? BigDecimal.valueOf(0) : $V{F})

)

To troubleshoot the issue with the SUM function, I define some additional variables to see what would happen if I put the value of the return variable into a second variable without any manipulation.  These new variables are also defined as java.math.BigDecimal.

$V{ATEST} expression is $V{A}

$V{BTEST} expression is $V{B}

$V{CTEST} expression is $V{C}

$V{DTEST} expression is $V{D}

$V{ETEST} expression is $V{E}

$V{FTEST} expression is $V{F}

This resulted in some unexpected behavior.  I printed the initial return values as well as the second set of variables on the report in the same band and got different results. 

Displaying $V{A} through $V{F} in group band:

A=10, B=10, C=0, D=10, E=-10, F=0           (perfect, exactly what I expected)

ATEST=10, BTEST=10, CTEST=0, DTEST=10, ETEST=null, FTEST=null    

It seems the negative value in $V{E} is not being carried over into $V{ETEST} resulting in null AND mysteriously affecting the value in $V{FTEST}. 

If all the variables return values, the variable with the negative variable $V{ETEST) still displays null as does $V{FTEST) BUT the sum works and correctly adds all the values. 

I am beginning to believe I have stumbled on a bug.  Any suggestions would be greatly appreciated.

Lori

[Tom C]

I suggest you to use Java BigDeciaml add() class method in text expression to carry out the calculation:

<textFieldExpression><![CDATA[new BigDecimal($V{Variable_a}==null?"0":$V{Variable_a}.toPlainString()).add($V{Variable_b}==null?new BigDecimal("0"):$V{Variable_b}).add($V{Variable_c}==null?new BigDecimal("0"):$V{Variable_c}).add($V{Variable_d}==null?new BigDecimal("0"):$V{Variable_d}).add($V{Variable_e}==null?new BigDecimal("0"):$V{Variable_e}).add($V{Variable_f}==null?new BigDecimal("0"):$V{Variable_f})]]></textFieldExpression> [/code]

Give a try and it should work.

[lori.stopka]

Thanks very much for the response. 

Initially I was going to respond that it didn't work because I had taken your expression and pasted it into my VARIABLE Expression and got the exact same result.  I then noticed that you had specfied a TEXT Expression so I used a text field instead and it worked.  I then went back to my original SUM and put that in a text field and it worked as well.

I took a look at the source code and found my original source contained the following:

    <variable name="TOTCOST" class="java.math.BigDecimal">
    <variableExpression><![CDATA[sUM(($V{TOTRECEIPTS} == null ? BigDecimal.valueOf(0) : $V{TOTRECEIPTS}),
    ($V{TOTISSUES} == null ? BigDecimal.valueOf(0) : ($V{TOTISSUES}*-1)),
    ($V{TOTXI} == null ? BigDecimal.valueOf(0) : $V{TOTXI}),
    ($V{TOTXO} == null ? BigDecimal.valueOf(0) : ($V{TOTXO})*-1),
    ($V{TOTADJQ} == null ? BigDecimal.valueOf(0) : $V{TOTADJQ}),
    ($V{TOTADJP} == null ? BigDecimal.valueOf(0) : $V{TOTADJP})
)]]></variableExpression>
 

After dragging a text box into the report and defining it with the same SUM expression, the source code shows:

     <textFieldExpression><![CDATA[sUM(($V{TOTRECEIPTS} == null ? BigDecimal.valueOf(0) : $V{TOTRECEIPTS}),
    ($V{TOTISSUES} == null ? BigDecimal.valueOf(0) : ($V{TOTISSUES}*-1)),
    ($V{TOTXI} == null ? BigDecimal.valueOf(0) : $V{TOTXI}),
    ($V{TOTXO} == null ? BigDecimal.valueOf(0) : ($V{TOTXO})*-1),
    ($V{TOTADJQ} == null ? BigDecimal.valueOf(0) : $V{TOTADJQ}),
    ($V{TOTADJP} == null ? BigDecimal.valueOf(0) : $V{TOTADJP})
)]]></textFieldExpression>
 

Do you have an explanation as to why the variableExpression gave the wrong results when zeros and/or negative values are involved?

 

[Tom C]

JasperReports engine carries out the calculation in a variable when a record is fetched, prior to a sub report call. Therefore your calculation will always be one iteration behind (current calculation can only get the return value from a prior sub report call). By putting the calculation in a text field in the group footer, we can assure the evaluation time is after the last sub report call thus be able to get all the values needed for your summary.

[lori.stopka]

Thanks very much for the explanation.