Grouping Data

Ad Hoc provides two styles of grouped reports - Flat-Table and Drill-Down. The grouped flat-table style organizes records into groups and hides duplicate entries to make the grouped report more presentable. Use the flat-table style to give other users the ability to export the complete report to different formats such as PDF and Excel. The grouped drill-down style organizes records into groups and any remaining columns are included in a sub-report. Use the drill-down style to give other users the ability to hide and show sub-reports.

The feasibility of grouping data highly depends on the data source chosen for the report. A grouped report is not necessarily appropriate when the data is simply a list of customers. If the data source contains information about customers and orders, a grouped report will definitely improve the presentation of the report. The application provides two ways to group data for flat-table and drill-down style reports - multiple grouping columns and multiple layers. Users can combine both methods when creating grouped reports.

Multiple Grouping Layers

Creating grouped reports with multiple layers is useful in scenarios where the report requires more than one grouping to organize all the data.

For example, all the customers can be grouped together, and then group by the shipping company and then group by the order date. This scenario is illustrated as a grouped flat-table report and as a grouped drill-down report in the following figures.

A grouped flat-table report with three layers.

A grouped drill-down report with three grouping layers.

Multi-Column Grouping

Multi-column grouping is useful for displaying more information on a single grouping layer. For example, users can group by customer and then by shipping company, showing both the customer and shipping company on the same layer.

Multiple grouping columns work well with flat-table style reports, since duplicate entries are removed from the final report.

A grouped flat-table report with two grouping columns.

Grouped Flat-Table

The grouping flat-table report style is useful for displaying the entire report in an organized, presentable way.

In the following figure, the Customer Name column is the grouping column and any row that contains a group is highlighted. All associated rows for a specific group are displayed beneath the grouping row. Aggregated values for each group are computed and displayed in a separate row.

A grouped flat-table report of customer invoices.

Grouped Drill-Down

The grouped drill-down report offers viewers the ability to control the appearance of the report by hiding and showing sub-reports for each record in the grouping column.

In the following figure, the Customer Name column is the grouping column (always the first column of the main report) and all other columns appear in the sub-report. Users can show/hide sub-reports for each group of records by clicking the hyperlink provided in the Details column. Any additional columns included in the main report are aggregations of a particular column from the sub-report.

A drill-down style report of customer invoices.

Note:

Users can disable drill-down functionality for the Details column or hide the column completely.

Grouping Layers

Creating and configuring a grouping layer:

  1. PREREQUISITE: If grouping aggregates are to be defined, then the column(s) to be aggregated must first be added to the report in the Table Columns tab.
  2. Click on the Grouping tab.

The Grouping tab with the Grouped Flat-Table style selected.

  1. Choose a grouping style (e.g., Flat-Table, Drill-Down) and then click Add Grouping Layer to display the Grouping Columns dialog.

  1. Select a column(s) from the Available Columns list-box and then click to move the column(s) to the Grouped Columns list-box. Hold the CTRL key down to select multiple columns.
  2. OPTIONAL: If a column(s) in the Grouped Columns list-box1 is not desired, select the column(s) in the Grouped Columns list-box and then click to move the column(s) back to Available Columns list-box . Hold the CTRL key down to select multiple columns.
  3. OPTIONAL: To move a column in the Grouped Columns list-box2 up or down, select the column(s) and click the or icon respectively.
  4. OPTIONAL: If using the flat-table grouping style, enable the 'Insert a page break between groups of PDF Export' option3.
  5. OPTIONAL: Click OK to retain the modification and exit the panel or continue configuring the other features on the grouping layer.

 

Notes:

1.    The Grouping Layer dialog keeps track of the grouped columns used.

2.    The order in which the columns appear in the Grouped Columns list-box dictates the order in which the columns will appear in the report. This display order over-rides that which is specified in the Column Configuration tab.

3.    This feature is only offered on the first layer of a grouped flat-table. If the Export to PDF feature is not used in the report, then enabling this feature will not have any effect on the report.

After having added columns to display in the tabular report and after having selected a column(s) to group the layer on, group aggregates can be created.

To create a grouping layer aggregate:

  1. From the Grouping Layer dialog, click the Add an Aggregate Column button. If the button is not visible, click on the Show All Attributes icon at the top of the dialog.

Grouping Columns dialog for a flat-table group

  1. Choose a Column from the drop-down menu.
  2. Choose an Aggregate function1, 2, 3 from the drop-down menu.
  3. OPTIONAL: If using the grouped flat-table style, type a descriptive name for the aggregate value in the Label field.
  4. OPTIONAL: If using the grouped drill-down style:
    1. Type a descriptive name for the aggregate column in the Header field.
    2. To include the column in the sub-report, enable the Include in sub-report option.
  5. OPTIONAL: If more than one aggregate has been specified in the grouping layer, click the or icon to arrange the order in which the aggregate will appear in the grouping column.
  6. OPTIONAL: Click to remove a grouping aggregate.
  7. OPTIONAL: Click OK to retain the modification and exit the panel or continue configuring the other features on the grouping layer.

Notes:

1.    For numeric values, choose aggregations like Sum, Average, Standard Deviation, Count, Count Distinct, Minimum, or Maximum.

2.    For text values, Sum and Average are excluded from the list of aggregate functions. Choosing Minimum selects the first alphabetized entry and Maximum selects the last.

3.    For date values, Maximum chooses the latest date and Minimum chooses the earliest.

Group aggregates in a flat-table report.

Group aggregates in a drill-down report.

When using the grouped drill-down style, the Grouping Layer dialog presents different options.

Grouping Columns dialog for a drill-style group

The Summary Column Options are described in the following table.

Option

Behavior

No summary

The report is rendered without a Details column and no means to drill-down to the sub-report.

Auto-generated column

(drill-down enabled)

The report is rendered with a Details column displaying a hyperlink with the count of the number of detail records contained in the sub-report. The hyperlink allows the user the ability to drill-down to the sub-report. This is the default option.

Auto-generated column

(drill-down disabled)

The report is rendered with a Details column displaying a count of the number of detail records contained in the sub-report. Drill-down is disabled.

Grouping / Aggregate column
(drill-down enabled)

The report is rendered without a Details column but displays one of the columns used in the grouping layer (i.e., grouped column or aggregate) as a hyperlink. The hyperlinked column allows the user the ability to drill-down to the sub-report.

When one of the auto-generated column display options is selected, the following options may be applied to the summary column:

To configure a grouped drill-down's Details column options:

  1. OPTIONAL: Choose a summary column display option. Refer to Table 2 for the explanation of the options.
  2. OPTIONAL: If the summary column display option of 'Grouping / Aggregate column' was selected, then choose a column from the drop-down menu.
  3. OPTIONAL: If either one of the 'Auto-generated column' summary column display options were selected, then:
    1. Type an alternate Column header name for the summary column in the field provided.
    2. Type an alternate Suffix name for the summary hyperlink in the field provided.
    3. Enable the Include summary row for Summary column option and type a descriptive Caption in the field provided.
  4. OPTIONAL: Click OK to retain the modification and exit the panel or continue configuring the other features of the grouping layer.

Grouped drill-down report with customized Details column heading, suffix and summary row

The application supports an unlimited number of grouping layers for any single report. Each layer can have multiple grouped columns, including any number of group aggregates.

Grouped drill-down report with three grouping layers

To modify a grouping layer (all options):

  1. Click on the Grouping tab.

  1. Choose a different grouping style (e.g., Flat-Table, Drill-Down).
  2. If more than one grouping layer has been defined, click the or icon to change grouping order in the report.
  3. Click the icon associated to the grouping layer to be modified.
  4. If modifying a grouped flat-table's first grouping layer, enable or disable the 'Insert a page break between groups of PDF Export' option.
  5. If modifying the lowest level grouping layer:
    1. Move columns between the Available Columns list-box and the Grouped Columns list-box by selecting the column(s) and clicking the or icons to move the column from one list-box to the other.
    2. Move a column in the Grouped Columns list-box up or down by selecting the column(s) and click the or icon respectively.
  6. If the grouping layer contain an aggregate:
    1. choose a different Aggregate function from the drop-down menu.
    2. If using the grouped flat-table style, modify the Label.
    3. If using the grouped drill-down style:

                                          i.    Modify the Header.

                                        ii.    Enable or disable the Include in sub-report option to include or not include the column in the sub-report.

    1. If more than one aggregate has been specified, click the or icon to arrange the order in which the aggregate will appear in the grouping column.
    2. Click to remove the aggregate.
  1. If using the grouped drill-down style:
    1. Choose a different summary column display option. Refer to Table 2 for the explanation of the options.
    2. If the summary column display option of 'Grouping / Aggregate column' was selected, then choose a column from the drop-down menu.
    3. If either one of the 'Auto-generated column' summary column display options were selected, then:

                                          i.    Type an alternate Column header name for the summary column in the field provided.

                                        ii.    Type an alternate Suffix name for the summary hyperlink in the field provided.

    1. Enable the Include summary row for Summary column option and type a descriptive Caption in the field provided.
  1. Click OK.

To delete a grouping layer:

  1. Navigate to the Table component's Grouping panel.
  2. Click the icon associated to the grouping layer to be deleted.