You can also add a group to the table by right-clicking a field and selecting Add as Group.
The following sections explain how to populate, edit, and format your View for a table-type report.
Insert data into your table by adding fields. All available fields are listed in the Data Selection panel, on the left side of the Ad Hoc Editor.
The available fields are divided into two sections in the panel:
• Fields, which can be added to the table as columns or groups.
• Measures, which are specialized fields that contain data values.
To add fields and measures as columns to a table:
1. In the Data Selection panel, click to select the field or measure you want to add to the table. Use Ctrl-click to select multiple items.
2. Drag the selected item into the Columns box in the Layout Band.
The field is added to the View as a column in the table.
To remove a field or measure from a table:
• In the Layout Band, click the x next to the field or measure’s name.
Groups allow you to create detailed data rows. For example, if you have a table that lists the names of suppliers for a restaurant, and you group the suppliers by the City field, the suppliers’ names are rearranged so that all suppliers located in Baltimore are together under a “Baltimore” header row, and all suppliers located in Bethesda are together under a “Bethesda” header row.
Only fields can be applied to a table as a group; measures cannot.
Data is grouped in the table according to the order they have defined. You can change the order by dragging the groups into position if needed.
To create a group:
1. In the Data Selection panel, click to select the field you want to add to the table as a group.
2. Drag the field to the Groups box in the Layout Band.
The Ad Hoc View refreshes and displays the data grouped under a new header row.
|
|
You can also add a group to the table by right-clicking a field and selecting Add as Group. |
To remove a group:
• In the Layout Band, click the x next to the field’s name in the Groups box.
To move a the grouping order up or down in a table:
• In the Layout Band, drag the name of the group you want to move into its new position.
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.
For example, in a table with a list of stores, grouped by City and Country, you can display the number of stores in each City, and in each Country, using this function.
By default, the summary function for each field is defined by the data source, OLAP, or domain definition.
To add a summary to a table:
• In the table, 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 table:
• In the table, right-click the column with the summary you want to remove, and select Remove Summary.
The summary information is removed from the table.
You can edit a column or header label directly in the Ad Hoc Editor.
To edit a column or header label:
1. On the Ad Hoc View panel, right-click the column or group header you want to rename.
2. Select Edit Label from the context menu.
The Edit Label window opens.
3. In the text entry box, delete the existing name and enter the new name.
4. Click Submit.
If space is at a premium, you can remove labels from the report. When you delete a label, it is displayed in the editor, but does not appear when you run the report.
To delete a column or header label:
1. On the Ad Hoc View, right-click the column or header label you want to remove.
2. Select Delete Label from the context menu.
To re-apply a label:
1. Right-click the column or header label you want to replace.
2. Select Add Label from the context menu.
The Edit Label window opens.
3. Enter the label name, if needed.
4. Click Submit.
You can change the size of, and spaces between, columns to manage the appearance of your table, to use space more efficiently.
To resize a column:
1. In the Ad Hoc View panel, click to select the column you want to resize.
2. Move the cursor to the right edge of the column.
3. When the cursor changes to the resize icon (
), click and drag the column edge right or left until the column is the needed size.
Spacers can be added to a table to arrange columns farther apart, or add margins to a table.
To change the spacing between columns:
1. In the Data Selection panel, in the Measures section, click Spacer.
2. Drag the spacer into the Columns box in the Layout Band between names of the two columns you want to move apart.
A spacer column, labeled 
, appears in the table.
3. Repeat this action to add space between each of the columns.
4. To remove a spacer, right-click the spacer column and select Remove from Table.
To use spacers to create table margins:
1. In the Data Selection panel, click to select Spacer.
2. Drag the spacer into the Columns box in the Layout Band.
3. Repeat as needed until the margins are as wide as needed.
4. Repeat the steps above, adding the spacer to the right edge of the table.
You can move columns to the right or left to reorder data in your table.
To reorder a column:
1. In the Ad Hoc View panel, right-click the column you want to move.
2. Select Move Right or Move Left from the context menu.
In the Ad Hoc Editor, you can sort the rows of a table by one or more fields.
To sort a table:
1. Click 
.
The Sort window appears. If the table is already sorted, the window shows the fields that are used.
2. To add a field to sort on, double-click the field in Available Fields.
The Available Fields panel now lists only fields that are not already in Sort On.
3. Select one or more fields to sort by. You can also use Ctrl-click to select multiple fields.
4. Click 
.
5. To arrange the sorting precedence of the fields, select each field in the Sort window and click Move to top, Move up, Move down, or Move to bottom: 
, 
, 
, and 
.
6. To remove a field, select it and click 
.
7. Click OK.
The table updates to display the rows sorted by the selected fields.
|
|
When sorting a table:
|
1. Above the table, click the text Click to add a title.
2. Enter the new table title in the text entry box.
You can change the formatting for columns containing numeric data, such as dates and monetary amounts.
To change the data format for a column:
1. In the Ad Hoc View, right-click the column for which you want to change the data format.
2. Select Change Data Format from the context menu.
3. Select the format you want to use. These options vary, depending on the type of numeric data contained in the column.
The data in the column now appears in the new format.
You may need to select a new data source for your table. This is a simple task, but you should keep in mind that all report data and formatting are lost when you select a new Topic, Domain, or OLAP connection. Any changes to the report are also lost if you navigate to another page using the browser navigation buttons, the main menu, or the Search field. To preserve changes, accept the current Topic or click Cancel.
To change the table’s data source:
1. At the top of the Data Selection panel, click 
and select Change Source.
2. Select a different Topic, Domain, or OLAP connection.
3. Click Table to apply the new data source.
Click Cancel to return to the editor without changing the Topic.
You can simplify or expand the information in your table by hiding or showing detail rows.
To hide detail rows in a table:
1. Place the cursor over 
.
2. Select Hide Detail Rows to show only the summarized totals for each group.
The Ad Hoc Editor applies a summary to each field depending on its datatype.
To show detail rows in a table:
1. Place the cursor over .
2. Select Show Detail Rows to display the detailed information for each group.
The Ad Hoc Editor displays the complete information in each row.