AX2665

About drill-through definitions

Drill-through definitions map the data in a specified target table to a column sequence in another data table. This allows users to "drill through" column data in the data table to the detailed data in the target table.

For example, imagine a report that displays actuals data from the GL2019 table. The user wants to see the detailed transactional data for the January 2019 value (column M1 from the GL2019 table). When the user selects to drill through that value, Axiom Software needs to know:

What table contains the associated detailed data for this value?

For example, you may have a table named GLDetail that holds the detailed transactional data for current year's actuals. Axiom Software needs to know that when you drill through data in the GL2019 table, the target table for this drill is GLDetail.

You may have one table that always holds the detailed data for the current year, or you may have multiple tables to hold detailed data per year (for example, GLDetail2019, GLDetail2018). You may also have multiple tables to hold different types of detailed data (for example, GLDetail, APDetail, RevDetail).

What filter needs to be applied to the target table to result in the associated detailed data for this value?

For actuals data and planning data, time is represented in the data table by using column sequences. For example, the GL2019 table may have a column sequence of M1-M12, representing 12 months of actuals data (aliases CYA1-CYA12).

However, data tables that hold detailed data typically define time as a "dimension," using a key column such as YearMo (year and month of the data). In order to drill from an actuals / plan data table to a detailed data table, Axiom Software needs to be able to map each column in the column sequence to the appropriate keys of the YearMo column.

For example, the first column of the sequence, M1, corresponds to YearMo values of 201901. This is represented as a filter applied to the detailed table: YearMo=201901.

Components of a drill-through definition

Each drill-through definition consists of the following:

  • The number of periods (columns) in the definition. This determines the number of filters to define, and the valid column sequences that you can map to. If you want to map to a 12 column sequence, then the definition must be 12 columns.

  • The target table for the definition. This is the table that data will be queried from when drilling through data.

  • The column filters to be applied when drilling. These column filters will be matched up to the column sequences when drilling. For example, the first column in the sequence matches up with the first column filter, and so on.

  • The column sequences to define the source data eligible for drilling. In most cases each drill-through definition will be associated with only one column sequence. For example, if the target table GLDetail contains transaction data for the current year, then it will probably only map to the sequence M1-M2 of the GL2019 table. However, if it makes sense for other sequences in the same table or in other tables to map to the same set of data, then you can do so.

  • The columns of the target table to display when drilling.

Requirements of the target table

There are no specific requirements for the structure of the target table, though typically this table will share certain key columns with the source tables for the mapped sequences, such as Acct and Dept.

What determines a valid drill-through context has more to do with the setup of the current report rather than the structure of the source and target tables. When a drill through is initiated from a report, a filter is applied to the drill-through table that includes the following:

  • The dimensionality of the current row being drilled (determined by the sum by of the Axiom query or the filter for the GetData function)
  • Any sheet filters defined for the sheet
  • Any filters applied to the Axiom query, such as the data filter or column filters (if drilling an Axiom query)

If any of these areas use columns that cannot be applied as filters to the target table, then the drill will fail with an invalid filter context. Therefore, the more similarities between the source and target tables (especially having common key columns with lookups), the greater the likelihood that the current dimensionality and filters applied in the report will apply to the target table. However it is also possible that the source and target tables could share only one common column, and as long as the dimensionality and filters of the report only use that common column, the drill-through context will be valid.

Drill-through example

The following shows an example drill-through definition, an example file eligible to be drilled, and the example drill-through results.

Example drill-through definition

Example report to be drilled

Example resulting drill sheet