Working with Crosstabs

In contrast to a table or tabular report showing individual records, a crosstab shows aggregate data for two or more variables in a tabular matrix.

You do not specify the individual row and column values for a crosstab at design time. Instead you specify fields in your dataset, called row or column groups, and your crosstab displays a row or column for each unique value of the field. This means the exact number of rows and columns remains undefined at design time and the crosstab automatically updates to reflect your dataset. A crosstab must include at least one row and one column group.

The cells in a crosstab show summary data for the corresponding row and column, based on a measure and a summary function. The simplest crosstab is a frequency matrix, such as the following example, which shows the count of pets (measure) by gender (column) and species (row).

Example of a simple crosstab

You can increase the complexity of a crosstab by adding more row or column groups, or by using another summary function, such as sum, average, or percent. For example, the following crosstab shows the sum of the monthly cost of food for each pet (measure) by gender (column) and group and species (rows). Note that when there are multiple row or column groups in a crosstab, they are displayed hierarchically.

Example of a crosstab with multiple row groups and a sum

Crosstabs in JasperReports support row and column groups, totals and subtotals, and individual cell formatting. Data to fill the crosstab can come from the main report dataset or from a subdataset.

This chapter has the following sections:

Example of Creating a Crosstab
Working with Crosstab Properties
Using the Crosstab Editor
Working with Crosstab Parameters

Example of Creating a Crosstab

When you add a Crosstab element to a report, Jaspersoft Studio displays the Crosstab Wizard automatically.

1. Create a new report:
a. Choose a blank template.
b. Select the Sample DB data adapter and click Next.
c. Enter the query select * from orders.
d. On the Fields page, select all fields and click Finish.
2. Because a crosstab summarizes information, you put it in the Summary band. For this example, delete all bands except the Title and Summary bands. This eliminates blank pages in the final report.
3. Drag the Crosstab tool into the Summary band. The Dataset page of the Crosstab wizard is displayed.

Dataset page of the Crosstab wizard

4. For this example, make sure that Create a Crosstab using an existing dataset is selected, and select [Main Dataset] from the drop-down menu.
5. Click Next. The Columns screen is displayed.
6. Enter one or more fields you want as column groups. For this example, choose the ORDERDATE field.

Defining column groups

7. Select the ORDERDATE field. Then click on the Unique value in the Calculation column and select Year from the drop-down menu. This aggregates the orders by year.

When you have a time field in your crosstab, you can use the Unique aggregation function to group records having the same value, or you can aggregate it any of the following ways:

Using a time-based aggregation function (such as Year, Month, Week, or Day) when you define the group

In this example, this is shown in the previous step.

Using a dataset query when you create the crosstab.

In this example, in the first step of the wizard, you could create a dataset that uses a query that returns the year, such as select ORDERDATE, SHIPVIA, SHIPPOSTALCODE, SHIPCOUNTRY, SHIPPEDDATE, YEAR(SHIPPEDDATE) as SHIPPEDYEAR from orders

Manually editing the element expression in the crosstab editor after the crosstab has been created, as described in Editing the expression of a group.

In this example, you could change the column element expression from $F{SHIPPEDDATE} to YEAR($F{SHIPPEDDATE}).

8. Click Next. The Rows screen is displayed.
9. Enter one or more fields you want as row groups. For this example, choose SHIPCOUNTRY and SHIPPOSTALCODE.

Defining row groups

10. Make sure that the fields appear in the order you want them in the crosstab. For this example, ensure that SHIPCOUNTRY appears first in the list by selecting it and clicking Up.

Grouping by SHIPCOUNTRY and then SHIPPOSTALCODE results in each row in the crosstab referring to a specific country, with subgroups by postal code within the country. Unlike in the main report, JasperReports will sort the data for you, although you can disable this function to speed up the fill process if your data is already sorted.

11. Click Next. The Measures screen is displayed.
12. Enter one or more fields you want as measures. For this example, choose ORDERID.

Measures define the detail data in the crosstab; normally, this is the result of an aggregation function like the count of orders by country by year, or the sum of freight for the same combination (country/ year). By default, the aggregate function is Count, which is what we want for this example. To change the aggregate function, you would select ORDERID, click on Count, and select a different value from the drop-down menu.

Defining measures

13. Click Next. The Layout page is displayed.
14. Set options for the crosstab layout. You can indicate whether you want to see grid lines, use color set to distinguish totals, headers, and detail cells, and whether to total the rows and columns.

For this example, select the Burleywood color scheme.

 

Choosing layout options

15. Click Finish.

The crosstab is created and added to your report.

Crosstab in Design view

16. Select the crosstab to display a border with handles. Drag the right-hand handle of the crosstab to the right margin of the report.
17. Preview your report. You see a crosstab. The row and column headers are the values of the fields you selected for the rows and columns. For each row and column, the value is the number of orders for that year and postal code.

Preview of the crosstab

Working with Crosstab Properties

To view or edit crosstab properties, select the crosstab node in the outline view. The properties are displayed in the properties view.

Expressions for elements in a crosstab, such as print-when expressions and text field expressions, can only contain measures. In this context, you cannot use fields, variables, or parameters directly; you always have to use a measure.

You can edit the following crosstab-specific properties on the Crosstab tab in the properties view:

Repeat Column Headers – When selected, the column headers are printed on every page when the crosstab spans additional pages.
Repeat Row Headers – When selected, the row headers are printed on every page when the crosstab spans additional pages.
Column Break Offset – Specifies the vertical space between sections of a crosstab when the crosstab exceeds the page width and two sections appear on the same page.

 

Column break offset

Using the Crosstab Editor

You can edit the fields, expressions, and layout of the crosstab in the crosstab editor. Like the report editor, the crosstab editor has a design view and an outline view. Using the crosstab editor you can:

Resize rows and columns and format individual cells
Add and delete row and column groups and edit group properties
Add, delete, and edit measures
Edit crosstab totals

To open the crosstab editor:

1. Double-click on the crosstab node in Outline view for the main report.

OR

2. Double-click on the crosstab in Design view for the main report.

When the crosstab editor is selected, a crosstab element is displayed in outline view. This crosstab element shows the whole crosstab structure, including the crosstab parameters and the row and column groups, measures, and cells.

Formatting Columns, Rows, and Cells

Manually resizing a row or column:

1. Open the crosstab editor.
2. Shift-click in the header of the row or column you want to change.

The row and column you selected are outlined.

Row and column selected in crosstab editor

3. Drag an outline to resize the row or column. Make sure that the cells are large enough to completely contain their content when you run the report.

Working with Cells

Each intersection between a row and a column defines a cell. Crosstabs have header cells, total cells, detail cells, and (optional) when-no-data cells. Each cell can contain one or more elements that do not use a dataset, such as text fields, static text, rectangles, and images. Cells can’t contain subreports, charts, or another crosstab.

Changing cell borders:

1. Open the crosstab editor and select the cell in editor or in outline view.
2. Edit the cell borders on the Borders tab of the property view.

Editing Row or Column Group Properties

You can edit the following properties for a row or column group:

Name – Name of the group. Renaming a group using the Properties dialog renames it everywhere the group is used.
Total Position – Location of the row or column that shows subtotals. Values are None, Start, End (default).
Order – Order of the values in the group (Ascending or Descending).
Order By Expression – Optional expression to use for ordering the values.
Comparator Expression – Optional instance of java.util.Comparator to use for ordering the values. If no expression is present, the default ordering for the data type is used (for example, numeric or alphabetic ordering). Expression – Bucket expression used to group the rows or columns. The default is to group by field value, for example, $F{SHIPPOSTALCODE}.
Value Class Name – Field type.

Editing the expression of a group:

The following example shows how to edit the sample crosstab to group by the first letter or digit of the postal code:

1. Double-click the crosstab to open the crosstab editor.
2. In Outline view, select the group you want to edit. For this example, select Crosstab > Row Groups > SHIPPOSTALCODE1.

Outline tree view – crosstab details in the crosstab editor

3. In Properties view, select the Cell tab.
4. Change the expression in the Expression entry bar to $F{SHIPPOSTALCODE}.substring(0,1).

 

Properties for SHIPPOSTALCODE1

When you preview the crosstab, the second row group is now bucketed by the first character of the postal code.

Crosstab after expression has been edited

Adding and Deleting Row and Column Groups

A crosstab must have at least one row group and one column group. It is easiest to add all the rows and columns you want when you create the crosstab with the Crosstab Wizard. However, if necessary, you can add a row or column group manually.

The following example shows how to add a row group, SHIPREGION, to the example crosstab. Adding a column is similar.

Example of adding a row group:

1. Double-click on the crosstab to open the crosstab editor.
2. In outline view, double-click the Crosstab node to expand it.
3. Right-click the Row Groups node and select Create Row Group from the context menu.

Adding a row group

The Group Band dialog is displayed.

4. Enter the information for your group in the Group Band dialog. For this example:
a. Enter SHIPREGION1 for the Group Name.
b. Select Create Group from a report object and select SHIPREGION.
c. Click Finish.

The new group is added to the crosstab as the innermost row group.

 

Group Band dialog

5. To set the value class of the group, select the top-level node of the new SHIPREGION group in outline view of the crosstab editor. Then, in the Cell tab of the properties view, enter the following value:
Value Class Namejava.lang.String

Setting Value Class Name of a row group

6. Change the order of the groups by selecting the top-level node of SHIPREGION in outline view and dragging it above SHIPPOSTALCODE.
7. Preview the report.

Deleting a row or column group:

1. Double-click on the crosstab to open the crosstab editor.
2. In outline view, double-click the Crosstab node to expand it.
3. Double-click the Row or Column Groups node to expand it.
4. Right-click on the row or column group you want to delete and select Delete from the menu.

Working with Measures

A measure in a crosstab is a object, similar to a variable, that appears in an individual cell. It is the result of a calculation performed on the values for each row and column group that intersect in a cell.

A crosstab can have multiple measures. If you add multiple measures when you first create a crosstab, each measure shows up under the Measure node in the outline view of the crosstab editor. You can also add measures after the crosstab has been created by dragging a text field into a measure cell in your crosstab and setting an expression. In this case, the measure is only visible in the Detail node of the outline view. For an example of adding a measure and setting its expression, see Adding a Measure as a Text Field.

Expressions for elements in a crosstab, such as print-when expressions and text field expressions, can only contain measures. In this context, you cannot use fields, variables, or parameters directly; you always have to use a measure.

Measure Properties

The following properties are available for measures that you added when you first created the crosstab:

Name – Name of the measure.
Calculation – Calculation to use for the measure. See Calculation Function for more information.
Percentage of Type – Set this to Grand Total to display your measure as a percentage of the grand total.
Value Expression – Expression to use for calculating the measure. To edit this expression, click .
Value Class – Java class to use for the expression.
Incrementer Factory Class Name – Optional custom Incrementer class. Use this to implement your own calculation if the available calculation types are not sufficient. Class must be instantiated via a factory that implements the net.sf.jasperreports.engine.fil.JRIncrementerFactory interface .
Percentage Calculation Class Name – Optional custom calculator class to perform the percentage calculation. Must use the net.sf.jasperreports.crosstabs.fill.JRPercentageCalculator interface.

To display measure properties:

1. Double-click the crosstab to open the crosstab editor.
2. Expand the Crosstab node in outline view.
3. Expand the Measures node.
4. Right-click the measure you want and select Show Properties from the menu.

Understanding Crosstab Total Variables

When you have multiple row or column groups, you can use crosstab total variables to combine data at different aggregation levels (for example, to calculate a percentage). The following built-in variables are available:

<Measure>_<Column Group>_ALL: The total of all the entries in the specified column group and the current row.
<Measure>_<Row Group>_ALL: The total of all the entries in the specified row group and the current column.
<Measure>_<Row Group>_<Column Group>_ALL: The combined total of all the entries in the specified row and column groups.

You can also select these variables from the expression editor for the Expression field on the Text Field tab of the Properties view for a measure.

Total variables in the expression editor

Adding a Measure as a Text Field

This example shows how to add a measure to an existing crosstab using a text field. This example uses crosstab total variables to calculate a percentage. However, measures added as text fields do not have measure properties such as a calculation or an incrementer calculation class.

Adding a measure:

1. Create a new report:
a. Choose a blank template.
b. Select the Sample DB data adapter and click Next.
c. Enter the query select * from orders and click Next.
d. On the Fields page, select all fields and click Finish.
2. Delete all bands except the Summary band. This eliminates blank pages in the final report.
3. Add a crosstab to the Summary band with the following settings:
a. Dataset – [Main Dataset].
b. Column group – ORDERDATE; select Year from the drop-down menu in the Calculation column.
c. Row group – SHIPCOUNTRY.
d. Measure – ORDERID.
4. In design view for the report, double-click on the crosstab to open the crosstab editor.
5. Shift-click in the second row and drag to expand the row height.
6. Drag a text field into the intersection of the first row and column.

The text field is added to the column.

Adding a text field to an existing measure

Setting the measure expression:

7. Select the text field you added.
8. Select the Text Field tab in Properties view.

Text field properties after setting the expression

9. Click  to the right of the Expression field to open the expression editor.
10. Add a formula to calculate the following percentage:

(Number of orders placed in this country and in this year)  /  (All orders placed in this country)

For Java, use the following expression:

new Double(
	$V{ORDERID_MEASURE1}.doubleValue()
	/
	$V{ORDERID_MEASURE1_ORDERDATE1_ALL}.doubleValue()
)

For Groovy, use the following expression:

(double)$V{ORDERID_MEASURE1} / (double)$V{ORDERID_MEASURE1_ORDERDATE1_ALL}

A percentage must be treated as a floating-point number. For this reason, extract the double-scalar values from ORDERID_MEASURE1 and ORDERID_MEASURE1_ORDERDATE1_ALL objects even if they are objects of class-type Integer.

11. Click Finish to close the expression editor.
12. Enter #,##0.00% in the Pattern field to format the result as a percentage.
13. Click Preview to run the report.

The final report with percentages included

Working with Crosstab Parameters

Crosstab parameters let you pass dynamic values from the main report to the crosstab as crosstab parameters. They can be used in the expressions of elements displayed in the crosstab. Crosstab parameters are defined and managed using outline view in the crosstab editor.

Crosstab parameters are designed to be used in crosstab elements. They are not the same as the dataset parameters that are used in expressions, in the crosstab context, to filter a query and calculate values.

To add a crosstab parameter:

1. Double-click your crosstab in design view to open the crosstab editor.
2. In outline view, right-click the Parameters node in the Crosstab element and select Create Parameter.
3. To set the value of the crosstab parameter, double-click the parameter to open the expression editor. Create an expression for your parameter and click OK.

You can use a map to set the value of the declared crosstab parameters at run time. In this case, you’ll need to provide a valid parameters map expression in the crosstab properties.

Version: 
Feedback
randomness