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).

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.

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

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

3.

Drag the Crosstab tool

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.

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.

For this example, select the Burleywood color scheme.

The crosstab is created and added to your report.

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:

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:

To open the crosstab editor:

OR

When the crosstab editor is selected, a crosstab element appears 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:

The row and column you selected are outlined.

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:

Editing Row or Column Group Properties

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

Editing the expression of a group:

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

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:

The Group Band dialog is displayed.

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

Deleting a row or column group:

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.

Measure Properties

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

•

Value Expression – Expression to use for calculating the measure. To edit this expression, click

To display measure properties:

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:

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.

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:

3.

Add a crosstab

6.

Drag a text field

The text field is added to the column.

Setting the measure expression:

9.

Click

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

For Java, use the following expression:

For Groovy, use the following expression:

(double)$V{ORDERID_MEASURE1} / (double)$V{ORDERID_MEASURE1_ORDERDATE1_ALL}[/code]                    

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.

To add a crosstab parameter:

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.