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;
}
}
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.