Sum values for distinct keys (without grouping)

Question

Background

Consider the following data set:

  Name   Mode   Tally
 ------ ------ -------
  N_1     M_1    1000
  N_2     M_3    4000
  N_3     M_2     500
  N_4     M_1    2000
  N_5     M_3    8000

The totals for Mode are:

  Mode   Total
 ------ -------
  M_1     3000
  M_2      500
  M_3    12000

The data is grouped by month and ordered by Name; it cannot be grouped by Mode. The entire set of values for Mode is unknown but finite (e.g., M_1, M_2, M_3, M_x, M_y, M_z, and so on).

Problem

The Mode totals must be presented in the Summary band, which looks like a good candidate for a variable incremented using a JRDistinctCountIncrementer (using an incrementer factory class name of JRDistinctCountIncrementerFactory). Part of the problem is that the documentation is lacking.

Example Output

To give a clear picture of the intended usage:

Note how the elements in the Tx Subtotals section re-use existing styles and align with existing columns. Writing String values to the Summary band using a Scriptlet would work, provided the Scriptlet can expose iterable data.

Approach 1

The list of totals summed for each distinct tuple (e.g., mode and tally) must be retrieved after the report rows have been filled. That list is then passed into a subreport as a JRMapCollectionDataSource. That subreport is placed on the Summary band of the main report.

For this, a variable must be created, along the lines of:

• Name: modes
• Value Class Name: java.util.Map ?
• Calculation: No Calculation Function ?
• Expression: new AbstractMap.SimpleEntry( $F{mode}, $F{tally} ) ?
• Initial Value Expression: ?
• Increment type: None
• Incrementer Factory Class Name: ?
• Reset type: Report

This would allow the subreport's Data Source Expression to be:

new JRMapCollectionDataSource( $V{modes} )

Approach 2

Create a MappedIncrementerFactory and MappedIncrementer, similar to JRDistinctCountIncrementerFactory and JRDistinctCountIncrementer.

Approach 3

Pre-calculate the grand totals and pass them in using a data object model. For example:

public class DataSetItem {
    public String getName() { ... }
    public String getMode() { ... }
    public Integer getTally() { ... }
}

public class DataSet {
    public List<DataSetItem> getDataSetItemList() { ... }
    public Map<String, Integer> getDataSetTotals() { ... }
}

public class DataSetFactory {
    /** Returns a single instance that has the list of items and totals. */
    public List<DataSet> createDataSetItemCollection() { ... }
}

Approach 4

Use a Scriptlet and return a JRDataSource for the values.

Question

How would you create a variable of type collection (a map) that contains key/value pairs where each value is the sum of report rows that match the key name?

Answer 1

A general solution follows. The names for the key and value column tuples are set as parameters in the subreport. The master report contains the scriptlet, the subreport, and the grand totals summary page. This is useful in the situation where there are a number of virtually identical subreports, but only some of them require grand totals based on column tuples.

Each subreport with grand total tuples must define a value for the key parameter and value for the value parameter: SCRIPTLET_KEY_COLUMN_NAME and SCRIPTLET_VALUE_COLUMN_NAME, respectively. If the key parameter isn't set in the subreport, then isSubreport() will return false and no summations will be performed.

Scriptlet

The master report runs the following scriptlet, which is configured by setting the Scriptlet Class to com.company.jasper.TupleSumScriptlet.

package com.company.jasper;

import java.util.*;
import net.sf.jasperreports.engine.*;
import net.sf.jasperreports.engine.data.JRBeanCollectionDataSource;

public class TupleSumScriplet extends JRDefaultScriptlet {
    private final static String REPORT_KEY_COLUMN_NAME
            = "SCRIPTLET_KEY_COLUMN_NAME";
    private final static String REPORT_VALUE_COLUMN_NAME
            = "SCRIPTLET_VALUE_COLUMN_NAME";

    private final Map<String, Integer> sums = new HashMap<>();

    public TupleSumScriplet() { }

    @Override
    public void afterDetailEval() throws JRScriptletException {
        if (isSubreport()) {
            final String keyColumnName = getKeyColumnName();
            final String key = (String) getFieldValue(keyColumnName);

            final String valueColumnName = getValueColumnName();
            final int value = (Integer) getFieldValue(valueColumnName);

            final Map<String, Integer> totals = getSums();
            final int sum = totals.containsKey(key) ? totals.get(key) : 0;

            totals.put(key, sum + value);
        }
    }

    public JRDataSource getDataSource() {
        return new JRBeanCollectionDataSource(sort(getSums()));
    }

    public Map<String, Integer> getSums() {
        return this.sums;
    }

    private String getKeyColumnName() throws JRScriptletException {
        return (String) getParameterValue(REPORT_KEY_COLUMN_NAME);
    }

    protected String getValueColumnName() throws JRScriptletException {
        return (String) getParameterValue(REPORT_VALUE_COLUMN_NAME);
    }

    private boolean isSubreport() {
        boolean result;

        try {
            result = true;
            final String unused = getKeyColumnName();

        } catch (JRScriptletException e) {
            result = false;
        }

        return result;
    }

    public static
            <K extends Comparable<? super K>, V> Collection<Map.Entry<K, V>>
            sort(Map<K, V> map) {
        final List<Map.Entry<K, V>> list = new LinkedList<>(map.entrySet());

        Collections.sort(list, new Comparator<Map.Entry<K, V>>() {
            @Override
            public int compare(Map.Entry<K, V> o1, Map.Entry<K, V> o2) {
                return (o1.getKey()).compareTo(o2.getKey());
            }
        });

        return list;
    }
}

Master Report

The JRXML for the master report includes:

• Detail band subreport
• Summary band Grand Totals subreport

The Detail band subreport element must have a REPORT_SCRIPLET parameter passed in using $P{REPORT_SCRIPTLET} from the master report.

The Summary band subreport element (Grand Totals) is trivial because it uses the API defined by Map.EntrySet<K, V>, which exposes getKey and getValue methods. These methods directly mapped to fields, which are defined and used in the subreport--as key and value, respectively. The element must also have its Data Source Expression set to:

$P{REPORT_SCRIPTLET}.getDataSource()

The relevant JRXML for the Grand Totals subreport follows:

<field name="key" class="java.lang.String">
    <fieldDescription><![CDATA[key]]></fieldDescription>
</field>
<field name="value" class="java.lang.Integer">
    <fieldDescription><![CDATA[value]]></fieldDescription>
</field>
<detail>
    <band height="15" splitType="Stretch">
        <property name="com.jaspersoft.studio.unit.height" value="pixel"/>
        <textField isBlankWhenNull="true">
            <reportElement x="0" y="0" width="75" height="15" isRemoveLineWhenBlank="true"/>
            <textFieldExpression><![CDATA[$F{key}]]></textFieldExpression>
        </textField>
        <textField isBlankWhenNull="true">
            <reportElement x="75" y="0" width="150" height="15" isRemoveLineWhenBlank="true"/>
            <textFieldExpression><![CDATA[$F{value}]]></textFieldExpression>
        </textField>
    </band>
</detail>

Example Output

This produces the desired results and requires no modifications to Java source code in the event that the tuple's column names change.