Home >

AX2312

Drill-down requirements

This topic explains the requirements to "drill down" the data in a spreadsheet Axiom file. Plan files and reports support drill-down drilling.

NOTE: You do not need to add a Drilling Control Sheet to a file in order to use the drill down features. The Drilling Control Sheet is only for the custom drilling feature.

Prerequisites

Although hierarchies are not required to perform drill-down drilling, in most cases you will want to define one or more hierarchies for your data. In addition to drilling, hierarchies are also used in the Filter Wizard for easier filter creation.

If you do not define hierarchies, then by default the only two drill-down options available are:

• All Detail: Drill to the "bottom" of the data, meaning all key columns.
• Choose Columns: Select any relevant column for drilling.

If you define hierarchies, then the relevant hierarchies for the data are available on the drilling menu along with the other drilling options. Users can select hierarchy levels for quick and intuitive drilling along predefined groupings.

To drill down by hierarchy, the desired hierarchies must be defined for the relevant reference tables. Hierarchies are based on the grouping columns in a reference table.

For example, you can set up hierarchies on the DEPT reference table for Geography (Country > Region > DEPT) and Management (VP > Manager > DEPT). These hierarchies will then be available when drilling data that uses DEPT as a lookup column. (If the data comes from multiple data tables, then DEPT must be a validated column in all of the data tables in order for the DEPT hierarchies to be available.)

Hierarchies are table-specific, and are managed when creating or editing a reference table. For more information, see Dimensions and hierarchies.

General drilling requirements

In all cases, the sheet must meet the following requirements in order for drilling to be available:

• The sheet must be defined on the Control Sheet.
• The Enable Drilling option must be On for the sheet. This is the default setting.

If the sheet is not defined on the Control Sheet, or if Enable Drilling is Off, then the Drill button will be disabled even if drillable content exists on the sheet.

Axiom query data rows that can be drilled

You can drill down data rows resulting from an Axiom query. The query must be enabled on the Control Sheet in order to be eligible for drilling, and the Drillable option for the query must be set to On (this is the default setting).

Note the following exceptions that cannot be drilled:

• Horizontal Axiom queries
• Axiom queries where the primary table is a system table (such as Axiom.Aliases)

All other Axiom queries can be drilled, however, design considerations and special behavior apply to certain Axiom query configurations. See Design considerations for drilling down Axiom queries.

GetData data rows that can be drilled

You can drill down data rows that contain one or more GetData functions. This means data rows that are unassociated with an Axiom query. GetData functions can also be used within an Axiom query, but in that case the drilling requirements and design considerations for Axiom queries apply.

Note the following exceptions that cannot be drilled:

• Rows where any of the GetData functions are nested within a larger formula. The GetData formula must be simply =GetData(arguments), or else the row is not eligible for drilling.
• Rows where the GetData functions query different data tables, and the tables do not share the same validated key columns. Basically, if the tables referenced in a row could not be queried together within an Axiom query, then the row is not eligible for drilling.
• Rows where all of the GetData functions query reference tables.
• Rows where one of the GetData functions queries a reference table that the primary data table for the row does not link to. The "primary" data table for this purpose is the first data table referenced in the row (from left to right).

All other GetData rows can be drilled, however, design considerations and special behavior apply to certain configurations. See Design considerations for drilling down GetData functions.