Ad Hoc Editor’s Standard Crosstab Layout

By default, a crosstab displays a subset of the data its query retrieves. Click Full Data to view the full dataset. This can be helpful in showing a more accurate view of the final view (by showing more data). Click Sample Data to return to the smaller subset and improve performance if you find the editor has slowed down.

Columns area

Drag dimensions and measures from the Data Selection panel to this area to create column groups. Drag fields to change the order of the groups. Measures must all be in the same area; you cannot have measures in the Columns and Row areas at the same time.

Rows area

Drag dimensions and measures from the Data Selection panel to this area to create row groups. Drag fields to change the order of the groups. Measures must all be in the same area; you cannot have measures in the Columns and Row areas at the same time.

Row and column
group labels

Displays the name of each field used for grouping. Right-click the group labels to use the context menu. When no row groups are defined, the words Row Group indicate this vertical region.

Row and column group values

Heading cells that show the group values. When there is more than one level of grouping, use the icons on the outer groups to expand or collapse the inner groups. Right-click a group value to exclude it or to keep only that value from among all group values of the same level.

Sorting controls

An icon beside a label shows the current sorting. Right-click a label to apply or change sorting. You can sort on multiple groups, but only one measure; sorting on a measure will reset all other measure columns to Don’t Sort.

Measure labels

Display the name of each measure in the crosstab. Right-click the measure label to change the summary function or data format of the measure.

Measures

Measures show an aggregated value in each cell of the crosstab, as well as row and column totals for each level of grouping. Click on a measure value to open an Ad Hoc table view in a new window showing the individual values that make up the aggregated value.

Set the granularity of date groups

When you select a date field as a group, you can specify the granularity of the group values. Right-click the group label, select Change Grouping and select Year, Quarter, Month, or Day.

Pivot a single group

To pivot any group from row to column or vice-versa, select the group by clicking its label, then drag it to the other area. You can also right-click the group label and select Move to Column Group for row groups or Move to Row Group for column groups.

Pivot entire crosstab

To pivot all row groups to column groups and vice-versa at the same time, click

Filtering

Right-click a group label and select Create Filter to slicing (Keep Only).

Keep Only

Slice out a single group by right-clicking its group value and selecting Keep Only. Use Ctrl-click to select multiple members to keep.

Exclude

Remove a group value from any group by right-clicking it and selecting Exclude. Use Ctrl-click to select multiple members to exclude.

Summaries (Totals)

By default, the crosstab includes grand totals of all row groups, shown in a Totals row at the bottom, and of all column groups, shown in a Totals column to the right.

To toggle the row totals, right-click the left-most row group and select Delete Row Summary or Add Row Summary. To toggle the column totals, right-click the top-most column group and select Delete Column Summary or Add Column Summary.

You cannot hide the inner totals of an expanded row or column group.

Change Summary Functions

Right-click a measure label and click Change Summary Function to select a summary function. For example, you may want to display an average rather than a total. For more information, see Summaries.

Column group limits

In some cases, the editor prompts you to confirm that you want it to return large amounts of data. When the number of column groups exceeds a configurable limit, the editor displays an ellipsis (...); its ToolTip indicates the number of remaining groups. Click the ellipsis to display them. For more information on configuration, see the JasperReports Server Administrator Guide.

Sorting

Crosstabs support multiple levels of sorting:

Drill to detail

Click a measure to drill to open an Ad Hoc table that displays a summarized column for each measure in your crosstab; the table is filtered by the group values for the measure you clicked. The original crosstab and the summarized table operate independently.