Ad Hoc Tables

The architecture of a table view consists of columns, rows and groups.

Columns in a table correspond to the columns in the data source. They are included by adding fields or measures to the table in the Ad Hoc view.

Rows correspond to rows in the database. The information in each row depends on what columns are included in the table. Using groups, rows can be grouped by identical values in any field with intermediate summaries for each grouped value.

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

Component	Description
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.
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:

Action	Description
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 left most column and the group labels in a table, drag a spacer to the left most 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 data set; click Sample Data to return to the smaller subset.

Summaries

You can display summary data for any column in your table. Summary data may be in the form of various functions, such as:

Sum
Count
Distinct Count
Average

To add a summary to a specific column, right-click the column you want to calculate a summary for, and select Add Summary. The summary information is added to the group header, or is added to the bottom of a column if no groups are included in the table.

To remove a summary from a specific column right-click the column with the summary you want to remove, and select Remove Summary. The summary information is removed from the table.

To add or remove summaries from all columns, click and select Detailed Data.