Hot questions for Using JasperReports in crosstab

Question:

I'm using crosstab report and after applying row and column sorting, it breaks column grouping. I call setDataPreSorted(true) on crosstab and SortBuilder for row and column:

SortBuilder rowSortBuilder = asc(field("rowOrder", Integer.class));
SortBuilder columnSortBuilder = asc(field("colOrder", Integer.class));
report.sortBy(rowSortBuilder, columnSortBuilder)

Here's how it supposed to look:

But the column grouping breaks and it looks like this:

Here's my full code:

import java.util.ArrayList;
import java.util.List;
import net.sf.dynamicreports.examples.Templates;
import net.sf.dynamicreports.jasper.builder.JasperReportBuilder;
import static net.sf.dynamicreports.report.builder.DynamicReports.asc;
import static net.sf.dynamicreports.report.builder.DynamicReports.cmp;
import static net.sf.dynamicreports.report.builder.DynamicReports.ctab;
import static net.sf.dynamicreports.report.builder.DynamicReports.field;
import static net.sf.dynamicreports.report.builder.DynamicReports.report;
import static net.sf.dynamicreports.report.builder.DynamicReports.stl;
import net.sf.dynamicreports.report.builder.SortBuilder;
import net.sf.dynamicreports.report.builder.component.Components;
import net.sf.dynamicreports.report.builder.crosstab.CrosstabBuilder;
import net.sf.dynamicreports.report.builder.crosstab.CrosstabColumnGroupBuilder;
import net.sf.dynamicreports.report.builder.crosstab.CrosstabMeasureBuilder;
import net.sf.dynamicreports.report.builder.crosstab.CrosstabRowGroupBuilder;
import net.sf.dynamicreports.report.builder.style.StyleBuilder;
import net.sf.dynamicreports.report.constant.Calculation;
import net.sf.dynamicreports.report.constant.HorizontalTextAlignment;
import net.sf.dynamicreports.report.constant.PageOrientation;
import net.sf.dynamicreports.report.constant.PageType;
import net.sf.dynamicreports.report.constant.VerticalTextAlignment;
import net.sf.dynamicreports.report.exception.DRException;
import net.sf.jasperreports.engine.data.JRBeanCollectionDataSource;

public class CrosstabGroupTest {

    public JasperReportBuilder build() throws DRException {

        JasperReportBuilder report = report();

        // styles
        String reportFont = "Roboto";
        int cellPadding = 3;

        StyleBuilder headerStyleCenter = stl.style().setFontName(reportFont).setFontSize(10).setBold(true)
            .setBorder(stl.pen1Point()).setPadding(cellPadding)
            .setHorizontalTextAlignment(HorizontalTextAlignment.CENTER)
            .setVerticalTextAlignment(VerticalTextAlignment.MIDDLE);

        StyleBuilder rowStyleData = stl.style().setFontName(reportFont).setFontSize(10)
                .setBorder(stl.pen1Point()).setPadding(cellPadding)
                .setHorizontalTextAlignment(HorizontalTextAlignment.CENTER)
                .setVerticalTextAlignment(VerticalTextAlignment.MIDDLE);

        // row groups
        CrosstabRowGroupBuilder<String> rowGroupItem = ctab.rowGroup("product", String.class).setShowTotal(false)
                .setHeaderStyle(rowStyleData).setHeaderWidth(120);
        CrosstabRowGroupBuilder<String> rowGroupUnit = ctab.rowGroup("unit", String.class).setShowTotal(false)
                .setHeaderStyle(rowStyleData).setHeaderWidth(50);
        CrosstabRowGroupBuilder<String> rowGroupDate = ctab.rowGroup("date", String.class).setShowTotal(false)
                .setHeaderStyle(rowStyleData).setHeaderWidth(50);

        // column groups
        CrosstabColumnGroupBuilder col_type = ctab.columnGroup("branch", String.class).setTotalHeader("Total quantity")
                .setShowTotal(true).setHeaderStyle(rowStyleData);

        // measure
        CrosstabMeasureBuilder quantityMeasure = ctab.measure("", "amount", Double.class, Calculation.NOTHING).setTitleStyle(stl.style().setFontSize(0));

        CrosstabBuilder crosstab = ctab.crosstab()
                .setCellWidth(220)
                .addHeaderCell(cmp.horizontalList(
                        cmp.text("Product").setStyle(headerStyleCenter).setFixedWidth(120),
                        cmp.text("Unit").setStyle(headerStyleCenter).setFixedWidth(50),
                        cmp.text("Year").setStyle(headerStyleCenter).setFixedWidth(50)).
                        setHeight(13))
                .setCellStyle(rowStyleData).setCellWidth(60)
                .rowGroups(rowGroupItem, rowGroupUnit, rowGroupDate)
                .columnGroups(col_type)
                .measures(quantityMeasure)
                .setDataPreSorted(true);

        // data
        List<ProductQuantityData> dataList = new ArrayList<>();
        int rowOrder = 1;
        int colOrder = 1;
        dataList.add(new ProductQuantityData("Water", "litre", "2016.02", "Branch 1", 50d, rowOrder, colOrder));
        dataList.add(new ProductQuantityData("Water", "litre", "2017.02", "Branch 1", 150d, rowOrder, colOrder));
        dataList.add(new ProductQuantityData("Water", "litre", "diff", "Branch 1", 100d, rowOrder, colOrder));

        colOrder = 2;
        dataList.add(new ProductQuantityData("Water", "litre", "2016.02", "Branch 2", 150d, rowOrder, colOrder));
        dataList.add(new ProductQuantityData("Water", "litre", "2017.02", "Branch 2", 140d, rowOrder, colOrder));
        dataList.add(new ProductQuantityData("Water", "litre", "diff", "Branch 2", -10d, rowOrder, colOrder));

        rowOrder = 2;
        colOrder = 1;
        dataList.add(new ProductQuantityData("Coffee bean", "kg", "2016.02", "Branch 1", 80d, rowOrder, colOrder));
        dataList.add(new ProductQuantityData("Coffee bean", "kg", "2017.02", "Branch 1", 75d, rowOrder, colOrder));
        dataList.add(new ProductQuantityData("Coffee bean", "kg", "diff", "Branch 1", -5d, rowOrder, colOrder));

        colOrder = 2;
        dataList.add(new ProductQuantityData("Coffee bean", "kg", "2016.02", "Branch 2", 77d, rowOrder, colOrder));
        dataList.add(new ProductQuantityData("Coffee bean", "kg", "2017.02", "Branch 2", 77d, rowOrder, colOrder));
        dataList.add(new ProductQuantityData("Coffee bean", "kg", "diff", "Branch 2", 0d, rowOrder, colOrder));

        // sort
        SortBuilder rowSortBuilder = asc(field("rowOrder", Integer.class));
        SortBuilder columnSortBuilder = asc(field("colOrder", Integer.class));

        report
                .sortBy(rowSortBuilder, columnSortBuilder)
                .setPageFormat(PageType.A4, PageOrientation.LANDSCAPE)
                .setTemplate(Templates.reportTemplate)
                .title(Components.text("Product quantity by branch"))
                .summary(crosstab)
                .pageFooter(Components.pageXslashY())
                .setDataSource(new JRBeanCollectionDataSource(dataList));

        return report;
    }

    public static void main(String[] args) {
        CrosstabGroupTest design = new CrosstabGroupTest();
        try {
            JasperReportBuilder report = design.build();
            report.show();
        } catch (DRException e) {
            e.printStackTrace();
        }
    }
}

Here's my java bean code:

public class ProductQuantityData {

    private String product;
    private String unit;
    private String date;

    private String branch;
    private Double amount;

    private int rowOrder;
    private int colOrder;

    public ProductQuantityData(String product, String unit, String date, String branch, Double amount, int rowOrder, int colOrder) {
        this.product = product;
        this.date = date;
        this.unit = unit;
        this.branch = branch;
        this.amount = amount;
        this.rowOrder = rowOrder;
        this.colOrder = colOrder;
    }

    public String getProduct() {
        return product;
    }

    public void setProduct(String product) {
        this.product = product;
    }

    public String getDate() {
        return date;
    }

    public void setDate(String date) {
        this.date = date;
    }

    public String getUnit() {
        return unit;
    }

    public void setUnit(String unit) {
        this.unit = unit;
    }

    public String getBranch() {
        return branch;
    }

    public void setBranch(String branch) {
        this.branch = branch;
    }

    public Double getAmount() {
        return amount;
    }

    public void setAmount(Double amount) {
        this.amount = amount;
    }

    public int getRowOrder() {
        return rowOrder;
    }

    public void setRowOrder(int rowOrder) {
        this.rowOrder = rowOrder;
    }

    public int getColOrder() {
        return colOrder;
    }

    public void setColOrder(int colOrder) {
        this.colOrder = colOrder;
    }
}

Answer:

Luckily found one helpful forum post.

Add the following 2 classes.

    public class CTOValueFormatter extends AbstractValueFormatter<String, String>{
        @Override
        public String format(String value, ReportParameters reportParameters) {
           return value.split("-")[1];
        }
    }

    public class CTOrderExpression extends AbstractComplexExpression<String> {

        public CTOrderExpression(String sortField, String showField) {
            addExpression(field(sortField, Integer.class));
            addExpression(field(showField, String.class));
        }

        @Override
        public String evaluate(List<?> list, ReportParameters rp) {
            return list.get(0) + "-" + list.get(1);
        }
    }

And do not use .sortBy(rowSortBuilder, columnSortBuilder) and .setDataPreSorted(true).

Instead create a row-group-item with the following code.

CrosstabRowGroupBuilder<String> rowGroupItem = ctab.rowGroup(new CTOrderExpression("rowOrder", "product")).setShowTotal(false)
    .setHeaderStyle(rowStyleData)
    .setHeaderWidth(120)
    .setHeaderValueFormatter(new CTOValueFormatter());

Also you can do same for the column-groups.

CrosstabColumnGroupBuilder col_type = ctab.columnGroup(new CTOrderExpression("colOrder", "branch")).setTotalHeader("Total quantity")
            .setShowTotal(true).setHeaderStyle(rowStyleData);

Grab the full source code here.

Question:

i'm working with jasper-report, i have this report i want to generate

i have created a class in java, in which i have a field natureIntervention displayed on the columns, atelier on the rows and numero used for the count in the crosstab

public class ChampEtatMaintenanceParNatureIntervention {

    private String natureIntervention;
    private String atelier; 
    private String numero;


}

what i'm trying to display is for each row atelier, a list of vehicule types (ex: lourds, legers,....)


Answer:

When generating crosstab with row and column groups on every record you need all information.

In your report is see

  • 2 row groups (atelier and vehicule type)
  • 1 column group (motor type)
  • value of crosstab column

This suggest the following getters on your java object (that you pass as datasource)

public class MyDatasourceClass {    
    public String getAtelier(){.. return ...};
    public String getVehiculeType(){.. return ...};
    public String getMotorType(){.. return ...};
    public String getValue(){.. return ...}; //this maybe is another class
}

And your cross tab groups will be

<rowGroup name="atelier" width="150">
    <bucket class="java.lang.String">
        <bucketExpression><![CDATA[$F{atelier}]]></bucketExpression>
    </bucket>
    ... header value....
</rowGroup>

<rowGroup name="vehiculeType" width="150">
    <bucket class="java.lang.String">
        <bucketExpression><![CDATA[$F{vehiculeType}]]></bucketExpression>
    </bucket>
    ... header value....
</rowGroup>

<columnGroup name="motorType" width="150">
    <bucket class="java.lang.String">
        <bucketExpression><![CDATA[$F{motorType}]]></bucketExpression>
    </bucket>
    ... header value....
</columnGroup>

Question:

The following image shows my crosstab in ireport:

I want to sort my columns in a specific order, knowing that in the crosstab data, i put a list of objects List<ChampEtatAteliers> with the following attributes :

public class ChampEtatAteliers {

    private String numero;
    private String denominationFr;
    private String etatEntreeSortie;

    public ChampEtatAteliers(String numero, String denominationFr, String etatEntreeSortie) {
        this.numero = numero;
        this.denominationFr = denominationFr;
        this.etatEntreeSortie = etatEntreeSortie;
    }
}

the etatEntreeSortie is shown in the columns, the denominationFr is shown in the rows, and numero is used for the count in crosstab

String rapportLien = "/SituationEquipement.jasper";
Map parametres = new HashMap();
JRBeanCollectionDataSource data = new JRBeanCollectionDataSource(atelierses);

in java I'm putting these objects in list atelierses List<ChampEtatAteliers>, so is there any way to sort the columns in the list or in the crosstab ireport, my xml code on ireport is as follows:

<columnGroup name="nbreVehicule" height="66">
    <bucket order="Descending" class="java.lang.String">
        <bucketExpression><![CDATA[$F{nbreVehicule}]]></bucketExpression>
        <orderByExpression><![CDATA[$V{MyOrderByField_measure}]]></orderByExpression>
    </bucket>
    <crosstabColumnHeader>
        <cellContents backcolor="#F0F8FF" mode="Opaque">
            <textField>
                <reportElement style="Crosstab Data Text" x="0" y="0" width="85" height="66" uuid="2109b261-1b20-493a-b664-ce59731c4a20"/>
                <box topPadding="1" leftPadding="1" bottomPadding="1" rightPadding="1"/>
                <textElement>
                    <font isBold="true"/>
                </textElement>
                <textFieldExpression><![CDATA[$V{nbreVehicule}]]></textFieldExpression>
            </textField>
        </cellContents>
    </crosstabColumnHeader>
    <crosstabTotalColumnHeader>
        <cellContents/>
    </crosstabTotalColumnHeader>
</columnGroup>
<measure name="MyOrderByField_measure" class="java.lang.Integer"  calculation="Nothing" >
    <measureExpression><![CDATA[$F{myOrderByField}]]></measureExpression>
</measure>

Answer:

In jasper report the crosstab columnGroup can be sorted ascending or descending by setting the order attribute on the bucket

Example

<columnGroup name="GROUP" height="16" headerPosition="Center">
    <bucket order="Descending" class="java.lang.String">
        <bucketExpression><![CDATA[$F{MyField}]]></bucketExpression>
    </bucket>
    .....
</columnGroup>

If you need a user defined order, you should use the orderByExpression

  1. Define a measure for on what you like to order

    <measure name="myOrderByField_measure" class="java.lang.Integer" calculation="First">
        <measureExpression><![CDATA[$F{myOrderByField}]]></measureExpression>
    </measure>
    
  2. Use the measure in orderByExpression

    <columnGroup name="GROUP" height="16" headerPosition="Center">
      <bucket class="java.lang.String">
        <bucketExpression><![CDATA[$F{myField}]]></bucketExpression>
        <orderByExpression><![CDATA[$V{myOrderByField_measure}]]></orderByExpression>
    </bucket>
    .....
    </columnGroup>
    

In your example this could be achieved by introducing a new field in the ChampEtatAteliers class example private int myOrderByField; and then via code setting it as needed.

Another solution, would be to return an own Object (class) as MyField and implement Comparable, that generates the desired order.

EDIT: WORK AROUND, OP CAN NOT MAKE THE orderByExpression work correctly.

Since the myOrderByField is unique (related 1 to 1) to the nbreVehicule he can use myOrderByField in the bucketExpression

<measure name="nbreVehicule_measure" class="java.lang.String">
   <measureExpression><![CDATA[$F{nbreVehicule}]]></measureExpression>
</measure>

<columnGroup name="nbreVehicule" height="66">
   <bucket class="java.lang.Integer">
     <bucketExpression><![CDATA[$F{myOrderByField}]]></bucketExpression>
   </bucket>
   ....
   <textFieldExpression><![CDATA[$V{nbreVehicule_measure}]]></textFieldExpression>
   .....
</columnGroup>

Hence the bucketExpression does not need to be the same as the text displayed in the crosstabColumnHeader

Question:

I'm wondering if there is a way to make a table like this using the JapserReports CrossTab or in any other way where the row group is the field value7.

The DataSet used is a JRBeanCollectionDataSource(list of SubViews).

Where SubView is:

public class SubView{

  //...

  private String value1;
  private String value2;
  private String value3;
  private String value4;
  private String value5;
  private String value6;
  private String value7;

  //...getters and setters...
}

Note that there is no need of a column group, but jasper doesn't compile successfully if I don't configure a column group (error message: Crosstab should have at least one column group).


Answer:

This to me does not seem like a crosstab problem. Crosstab's are used when the columns are dynamic, hence your error message

Crosstab should have at least one column group.

since there is "no need of a column group" the result is achived using the normal detail band and grouping on value7.

So after you put the labels in the columnHeader and the values 1-6 in the detail band you add a grouping on value7 with a groupHeader band

Example (I have only included until value 3 in detail band to sorten the answer

<?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" printOrder="Horizontal" pageWidth="595" pageHeight="842" whenNoDataType="AllSectionsNoDetail" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" isFloatColumnFooter="true" uuid="8944f542-2955-4224-933c-5a87fd36f527">
<queryString>
    <![CDATA[]]>
</queryString>
<field name="value1" class="java.lang.String"/>
<field name="value2" class="java.lang.String"/>
<field name="value3" class="java.lang.String"/>
<field name="value7" class="java.lang.String"/>
<group name="Value7">
    <groupExpression><![CDATA[$F{value7}]]></groupExpression>
    <groupHeader>
        <band height="21">
            <textField>
                <reportElement x="0" y="0" width="300" height="20" uuid="208ca6e5-b396-47cb-858b-71039f5bdf7a"/>
                <textFieldExpression><![CDATA["Value 7: " + $F{value7}]]></textFieldExpression>
            </textField>
        </band>
    </groupHeader>
</group>
<background>
    <band/>
</background>
<columnHeader>
    <band height="50">
        <staticText>
            <reportElement x="0" y="30" width="100" height="20" uuid="edf0f0f1-c979-4d20-987e-e8decb0b584a"/>
            <text><![CDATA[value1]]></text>
        </staticText>
        <staticText>
            <reportElement x="100" y="30" width="100" height="20" uuid="4287323f-e6d3-40d8-a2d4-44981bfa5c59"/>
            <text><![CDATA[value2]]></text>
        </staticText>
        <staticText>
            <reportElement x="200" y="30" width="100" height="20" uuid="b9aa7dfd-edd8-439b-b4c7-12916a740da8"/>
            <text><![CDATA[value3]]></text>
        </staticText>
    </band>
</columnHeader>
<detail>
    <band height="24">
        <textField>
            <reportElement x="0" y="0" width="100" height="20" uuid="2bcc436d-4c3a-4db1-a2ea-87edf92af98f"/>
            <textFieldExpression><![CDATA[$F{value1}]]></textFieldExpression>
        </textField>
        <textField>
            <reportElement x="100" y="0" width="100" height="20" uuid="f893721e-1aa6-44ca-9a9a-ac795ff63a0a"/>
            <textFieldExpression><![CDATA[$F{value2}]]></textFieldExpression>
        </textField>
        <textField>
            <reportElement x="200" y="0" width="100" height="20" uuid="36ad9612-620b-4eec-9608-f26b5306b01a"/>
            <textFieldExpression><![CDATA[$F{value3}]]></textFieldExpression>
        </textField>
    </band>
</detail>
</jasperReport>

If you like to use crosstab anyway, you need to setup your dataset differently (since also columns become dynamic) check out this for examples: crosstab example