Using the Grid Tool

How to:

Use the Grid Tool

Reference:

Grid Tool Usage Notes

The Grid Tool enables you to change the column order, select multiple columns to sort ascending or descending, hide and show columns, add a calculation result to a column, and add subtotals in the active report.

This topic describes how to show the Grid Tool menu option when an active report is run, and how to use the Grid Tool.

The Grid Tool is available as long as you do not include the following StyleSheet declaration in the request:

TYPE=REPORT,ALLOW-GRID=OFF,$

Procedure: How to Use the Grid Tool

Run the active report, click the arrow in any column heading, and select Grid Tool.

The Grid Tool opens, as shown in the image below.

The columns are displayed in the order that they appear in the active report.

Tip: You may left-click and drag the columns in the Column Order section to reorder the list.
To hide or show columns, click the Hide Column icon next to the column name.

By default, the Grid Tool displays Hide icons for the hidden fields (HIDE=ON StyleSheet setting) and NOPRINT fields in the procedure. The Grid Tool displays Show icons for anything else.
To add a calculated result to the column, click the calculation icon next to the column name. You may assign a different calculation result for each field.
There are no calculations set by default.
- Sum, Avg, Min, Max, Count, or Distinct are available for numeric fields.
- Min, Max, Count, or Distinct are available for Date type fields.
- Count or Distinct are available for non-numeric alpha string fields.
- None indicates that there will be no aggregation performed or applied for the column in the report. When None is selected, Detail appears next to the calculation icon, implying that the report will display detailed data for this column in the report.
Left-click and drag the columns from the Column Order into the Sort Order section. You may also double-click to add columns.

When columns are added to the Sort Order section, options for sorting ascending or descending appear. The default sort order is ascending, lowest to highest (A to Z). Click the sort order icon to switch to descending, highest to lowest (Z to A).

In the example below, COUNTRY is descending and CAR is ascending.
You may edit the sort fields by clicking the X icon to delete columns, drag multiple columns into the Sort Order section, reorder the sort fields, and group sort columns.
Click Group sort columns to group the report by columns in the Sort Order section.

A Subtotal column appears in the Sort Order section. No subtotals are selected by default.

When Group sort columns is selected, the columns in the report are grouped by the order specified in the Sort Order section. These columns are repositioned to the beginning of the report and override the Column Order list.
To add a subtotal for an aggregated column.
- Select the Subtotal check box next to the column name.
- Click Group sort columns again to hide the Subtotal column.
In the following example, MODEL and SEATS are hidden, DEALER_COST and RETAIL_COST have a Sum aggregation type, COUNTRY, CAR, MODEL, and SEATS appear in the Sort Order column, Group sort columns is selected, and Subtotal is applied to CAR.
Click OK to close the Grid Tool.

The active report is generated based on the sort fields selected.

In the example below, the subtotal of sum values appear under DEALER_COST and RETAIL_COST by the CAR field. You may select Grid Tool from the column menu to open the Grid Tool again.

Reference: Grid Tool Usage Notes

The following apply when you use the Grid Tool in active reports:

The same column can appear in both the Column Order and Sort Order section.
Column Order displays the order of how columns appear in the report, unless Group sort columns is selected. The Sort Order list overrides the Column Order if Group sort columns is selected.
The Column Order and Sort Order sections can contain field names in a different order, as long as Group sort columns is not selected.
Columns can be dragged from Column Order to Sort Order but they cannot be dragged from Sort Order to Column Order.
Sort ascending is the default sort order, when columns are added to the Sort Order section.
There are no calculations set by default. Sum, Avg, Min, Max, Count, or Distinct are available for numeric fields. Min, Max, Count, or Distinct are available for Date type fields. Count or Distinct are available for non-numeric alpha string fields. None indicates that there will be no aggregation performed or applied for the column in the report.
When the Subtotal option is selected and an aggregation type is selected in the Column Order, subtotals display by the corresponding sort field (for the fields that have calculations specified).
Total plus the aggregation type (Cnt, Sum, and so on) appears with the column in the report output when Subtotal is selected.
You may not add additional columns to Sort Order once Group sort columns is selected. Deselect Group sort columns to add additional columns.
If a column is hidden and appears in the Sort Order section, the subtotal value will also be hidden if Group sort columns is selected.
When a filter is applied to the report, the subtotal will display the filtered value.
Subtotals are not supported with date fields.
The active report Sort Ascending and Sort Descending menus will override the options set in the Sort Order section of the Grid Tool.
The subtotal background color and font color can be changed by using CALC-AREA object in the STYLE section of the procedure. For example:
```
TYPE=REPORT,OBJECT=CALC-AREA,COLOR=WHITE,BACKCOLOR=BLACK,$
```
The font style for the subtotal values can be changed by using TITLE type in the STYLE section of the procedure. For example:
```
TYPE=TITLE,FONT='GEORGIA',COLOR=RGB(78 137 187),$
```