Ad Hoc Tables

The following figure illustrates the canvas when working on Ad Hoc tables with Details and Totals selected:

Ad Hoc Editor’s Table Layout



Columns area

Drag fields and measures from the Data Selection panel to this area to create columns.

Groups area

Drag fields from the Data Selection panel to this area to create groups. Measures cannot be added to the Groups area.

Canvas Options

Click to select Detailed Data (default), Totals Data, or Details and Totals.

Column labels

Displays the label for each column above the table in a header row. Highlight the column and right-click to change or remove the label. When you remove a column label, its database name is shown in the editor, but the label does not appear in reports created from the view.

Group header

Displays the label of the group and its current value. Groups and sub-groups can be nested, and their first occurrence headers are all found at the top of the view. Right-click the first occurrence of a group to access group options such as removing the group from the table, changing the label, or creating a filter on that field. You can reorder the first headers to change the group nesting order.

Group summary

If Totals Data or Details and Totals is selected, shows the group value and group total, if any. To select a different summary function, modify the column summary.

Column Summary

Gives a total value for all the rows in the column. To see all summaries, select Totals Data or Details and Totals from the Canvas Options menu. To add or remove a summary, right-click on the column and select Add Summary or Remove Summary. To select a different summary function, right-click on the column and select Change Function; for more information, see Summaries.

Column of Values

A vertical region in the canvas representing data from a single field. Right click on a column to access column options such as sorting on that column, adding or removing a column summary, changing the column’s data format or label, and creating a custom field or filter based on the column’s field.

Common tasks when working with Ad Hoc tables include:



Resize a column

Click the right-hand border of the column header and drag it horizontally. For more precision, click the column to highlight its borders before dragging them.

The minimum width of a column is determined by its longest visible member (be it a row or the label itself). When the data doesn’t fit the column width, it wraps (in Excel and HTML formats) or is truncated (in PDF format). By default, the canvas only displays the first 15 rows of data. To verify that the column widths are sufficient, click Full Data to display the full set of data.

Add blank columns

To add white space between columns, drag the Spacer from the list of available measures and drop it in the Columns area. Drag the spacer’s edges to widen or narrow it. You can add any number of spacers to a view.

To create space between the leftmost column and the group labels in a table, drag a spacer to the leftmost position; the margin provides a buffer between the first column and the groups summaries.

Sort a column

Click or right click on a column in the Canvas and select Use for Sorting. You can add fields and change the sort direction from ascending to descending. You can sort by multiple fields, including those not displayed in the view.

Filter a column

Right-click a field or a column in the layout band or the Canvas and select Create Filter to filter your results by the values in that field. To select a filter operation other than the default, click the filter’s icon and select Toggle Operation.

Show all groups

When a table is grouped by multiple fields, you may only see a few groups or even a single, partial group. Click Full Data to view the full dataset; click Sample Data to return to the smaller subset.