Friday, September 12, 2014

Summarize from subreports in JasperReport

One from typically reporting tasks is calculate aggregate operations: summarize, counting, averaging. JasperReport allow run this operation. But if you work with subreports you can meet with strange behavior. Below I will tell about one aggregate operations - calculating sums and pay attention upon not evident moments.

Main principle of summarizing is passing sum value from subreport to main report and accumulate it in accord value on main report. We can modify sample from previous article for get such result:
Picture1: Report with sums.


Modify subreport.

Open java_beans_datasource_report_subreport1.jrxml with iReport. In Variables  branch add values with such parameters:
  • Name: Sum; Variable class: java.lang.Double ;  Calculation: Nothing ; Variable Expression: $F{price}*$F{quantity} ; Tis variable for calculating sum of position.
  • Name: sumAllQ; Variable class: java.lang.Integer Calculation: Sum ; Variable Expression: $F{quantity} ; This variable for accumulate sum of quantiy.
  • Name: sumAllS; Variable class: java.lang.Double Calculation: Sum ; Variable Expression: $V{Sum} ; This variable for accumulate sum of Sum variable.
  • Name: vFormattedSumAllS; Variable class: java.lang.String ;  Calculation: Nothing ; Variable Expression:
    new java.text.DecimalFormat("#,##0.00").format($V{sumAllS}.doubleValue())
    
    This variable for setting double variable pretty view.
Next modify subreport design as below:
Picture 2: Add variables to subreport.

After compile report and we must

Modify main report

Open java_beans_datasource_report.jrxml and add next variables:
  • Name: totalSumQ; Variable class: java.lang.Integer Calculation: Sum ;  This variable for accumulate quantity from subreport. Note: Variable Expression is empty.
  • Name: totalSumS; Variable class: java.lang.Double Calculation: Sum ; This variable for accumulate sum from subreport.
    Note: Variable Expression is empty.
  • Name: formattedTotalSumS; Variable class: java.lang.String ;  Calculation: Nothing ; Variable Expression:
    new java.text.DecimalFormat("#,##0.00").format($V{totalSumS}.doubleValue())
    
    This variable for setting double variable pretty view.
 Now we can setup variables exchange between subreport and main report. Select Subreport component and go to Properties. Find Return Values and press button. In appropriate dialog press Add button. Set
  •  Set the Subreport variable to sumAllQ and the Local destination variable to totalSumQ. Also set Calculation type to Sum.
  • Set the Subreport variable to sumAllS and the Local destination variable to totalSumS. Also set Calculation type to Sum.
Picture 3: Setup variables exchange.

Important: Properties Calculation of totalSumQ, totalSumS and Subreport variable of Subreport ReturnValues must be Sum.

Enable Summary band and add totalSumQ and formattedTotalSumS vatiables.
Picture 4: Add variables to Summary band.

Now, after press Preview button you can see result from Picture1.

Downloads:

You can download source code from  BitBucket.

Related posts:

Credits:

Creating jasper sub-report using iReport by Rajendra M. Tambat.