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
See also Multiple measure Crosstabs
Refer to CrosstabReportTest4 for a working example.
Also, these other examples show other ways to create crosstabs and add them to group headers and footers