AX2317

How the "drill-through" drill sheet is created

When you initiate a drill through, Axiom Software creates a new, temporary file that contains a drill sheet with the drilling results.

Drill data

On the drill sheet, the drill data is returned by creating an Axiom query. Axiom Software creates the query as follows:

  • The primary table for the query is the target table from the drill-through definition.
  • A field definition row is created that contains the drill target columns from the drill-through definition.
  • A data filter is applied to the query, based on the following:

    • The column filter in the drill-through definition that corresponds to the column being drilled in the source file. The column being drilled is determined from either the field definition row (if drilling an Axiom query) or the column parameter of the GetData function.

      For example, if you are drilling a value from the CYA1 column, and the drill-through definition has a column 1 filter of YearMo=201101, that filter is applied to the drill query.

    • The dimensionality of the row being drilled in the source file. The row dimensionality is determined from either the data control column (if drilling an Axiom query) or the filter parameter of the GetData function.

      For example, if you are drilling a value that displays revenue account data, the filter Acct.Category='Revenue' is applied to the drill query.

  • Any filters applied to the original value are also applied to the drill query. This includes:
    • Any sheet filters on the original sheet.
    • If drilling an Axiom query, the data filter for the original Axiom query.
    • If drilling an Axiom query, any column filter defined on the field definition for the value being drilled.

Headers

Headers are created on the drill sheet as follows:

  • The top of the sheet contains a header that identifies the sheet as a drill-through sheet, and identifies the following:
    • Drill to: The target table and the column filter applied to the data, both derived from the drill-through definition.
    • Source: The source dimensionality of the value from the original report.
  • Each column of the drill data is labeled with the column name.

Freeze panes

Freeze panes are set on the drill sheet to the left and top of the first non-key column.

The key columns included and their locations depend on the drill target column order set in the drill-through definition. Typically, key columns are displayed first, in the left-hand side of the sheet, followed by the data columns. Panes are frozen after the last key column displayed in the left-hand side of the sheet.

If no key columns are displayed to the left (meaning, the first column displayed is a non-key data column), then no frozen panes are set.

Subtotals

Subtotals are placed in the drill sheet, below the drill data, but only for decimal and numeric columns. Integer columns (all types) are not subtotaled, as they may contain descriptive data (such as a transaction ID number) instead of amounts. If you want data to be subtotaled automatically in the drill sheet, it must be a Numeric column.