Posted in Crosstabs

HOW-TO Crosstabs

Crosstabs are a great feature introduced by JasperReports, now it is possible to create crosstabs through DynamicJasper.

The above crosstab was generated with a fragment like this:

FastReportBuilder drb = new FastReportBuilder();
        drb
        .setTitle("November 2006 sales report")
        .setSubtitle("This report was generated at " + new Date())
        .setPageSizeAndOrientation(Page.Page_A4_Landscape())
        .setPrintColumnNames(false)
        .setUseFullPageWidth(true)
        .setDefaultStyles(titleStyle, null, null, null);

DJCrosstab djcross = new CrosstabBuilder()
        .setHeight(200)
        .setWidth(500)
        .setHeaderStyle(mainHeaderStyle)
        .setDatasource("sr",DJConstants.DATA_SOURCE_ORIGIN_PARAMETER, DJConstants.DATA_SOURCE_TYPE_COLLECTION)
        .setUseFullWidth(true)
        .setColorScheme(DJConstants.COLOR_SCHEMA_LIGHT_GREEN)
        .setAutomaticTitle(true)
        .setCellBorder(Border.THIN)
        .addColumn("State","state",String.class.getName(),false)
        .addColumn("Branch","branch",String.class.getName(),false)
        .addRow("Product Line", "productLine", String.class.getName(),false)
        .addRow("Item", "item", String.class.getName(),true)
        .addMeasure("amount",Float.class.getName(), ColumnsGroupVariableOperation.SUM , "Amount",measureStyle)
        .setRowStyles(colAndRowHeaderStyle, totalStyle, totalHeaderStyle)
        .setColumnStyles(colAndRowHeaderStyle, totalStyle, totalHeaderStyle)
        .setCellDimension(17, 60)
        .setColumnHeaderHeight(30)
        .setRowHeaderWidth(80)
        .build();

drb.addHeaderCrosstab(djcross); //add the crosstab in the header band of the report

DynamicReport dr = drb.build();

//put a collection in the parameters map to be used by the crosstab
params.put("sr", SortUtils.sortCollection(TestRepositoryProducts.getDummyCollection(),djcross));

There is no limit about how many columns or rows the crosstab contains.

The data must be sorted with the following criteria: row(1), …, row(n), column(1), …, column(m)

Where:

row(1) will be the outer-most row, row(n) will be the inner-most row
column(1) will be the outer-most column, column(m) will be the inner-most column

The key calls in the CrosstabBuilder are:

setDatasource(<path to the data source>, <origin of the data source>, <type of the data source>)Where:
- path to the data source: A string that points to the data source.
- origin of the data source: A constant from DJConstants class which can be: SUBREPORT_DATA_SOURCE_ORIGIN_FIELD or SUBREPORT_DATA_SOURCE_ORIGIN_PARAMETER, it just tells the subreport where to look for the data source (the parameters map, or if its a property of the current row)For example, if path to the data source is “subreportDatasource” and origin of the data source is SUBREPORT_DATA_SOURCE_ORIGIN_PARAMETER, then it will look in the parameters map for an object under the key of “subreportDatasource”, on the other hand if we pass SUBREPORT_DATA_SOURCE_ORIGIN_FIELD, in the current row, there must be a property with the name “subreportDatasource” that returns an object of the type DATA_SOURCE_TYPE_<XXX>
- type of the data source: A constant from DJConstants class, all types constants has a name like DATA_SOURCE_TYPE_<XXX>, where <XXX> represents the type. Most known types are java.sql.ResultSet, Collection, Array, Xml, etc.
addColumn(<title>,<property>,<value Class Name>,<show Totals>)Where:
- Title is the title to show in the column header total
- property is the property to use in the column
- value Class Name is the class name of the property
- show Totals When true, when the column changes its value, it adds a total column showing the total for that column
addRow(<title>,<property>,<value Class Name>,<show Totals>)Where:
- Title is the title to show in the row header total
- property is the property to use in the row
- value Class Name is the class name of the property
- show Totals When true, when the row changes its value, it adds a total column showing the total for that row
addMeasure(<property>, <value Class Name>, <operation> , <title> , <style>)Where:
- property is the property to operate with
- value Class Name is the class name of the property
- operation is any of the constants of the ColumnsGroupVariableOperation class, it tells what operation to apply to the property
- title is the name of the items that property represents
setCellDimension(<height>, <width>)Defines the dimension of the inner-most cell