Home >

On this page

AX2309

Design considerations for drilling down Axiom queries

The following design considerations and special behaviors apply when drilling down the data in an Axiom query. For more information on how the drill sheet itself is created, see How the "drill-down" drill sheet is created.

Axiom query setup

If the field definition row and/or the in-sheet calc method row for the query uses formulas and cell references to locations on the same sheet but outside of these rows, then you must do one of the following to ensure that the formulas work on the resulting drill sheet:

  • Enclose the "setup" area and all referenced cells within frozen panes. In this case, the entire frozen area (including rows "hidden" above the freeze panes point) is copied to the drill sheet, so the formulas will still work.
  • Place the referenced cells above the field definition row and in-sheet calc method. If freeze panes is not used on the sheet, then the area from the top of the sheet down to the last row of either the field definition or the in-sheet calc method is copied to the drill sheet, so the formulas will still work. If the formulas reference cells that are below the field definition or the in-sheet calc method, then the formulas will not work.

For example, if your field definitions point to other cells to dynamically construct column names (such as CYA1), then those referenced cells must either be within the freeze panes area, or located above the field definition row / in-sheet calc method (whichever one is lowest on the sheet).

Drilling behavior for certain Axiom query configurations

Note the following drilling behavior with certain Axiom query configurations:

Configuration Drilling behavior

Multiple-row calc methods

If the Axiom query uses a multiple-row calc method, the drill results present the drilled data for all rows of the calc method, not just the row that was drilled. Subtotals are not applied to the data. Also, if freeze panes is not set, the column headers are created using the last column name that occurs in each field definition column.

Calc method library

If the query uses a calc method library instead of an in-sheet calc method, then the drill results use the calc method that is applied to the row being drilled (based on the calc method validation column).

If the sheet does not use validation (and therefore there is no calc method validation column), then the Default Calc Method for the query is used. If the sheet does not use validation and there is no default calc method, then no calc method is applied to the drill results.

This only applies to file group files that have access to a calc method library, such as plan files and file group utilities.

Nested Axiom queries

You can drill nested Axiom queries. A nested Axiom query is where the in-sheet calc method of one query is used to build out a second "child" query.

The drill results will return data for the Axiom query that the drilled row belongs to. For example, if AQ1 builds out multiple data ranges for AQ2, and you drill a row within an AQ2 data range, then the drill results will be for AQ2. Results depend on how the queries are set up, but in most cases this should return the drill results that you are expecting.

Parallel Axiom queries

You can drill parallel Axiom queries. A parallel Axiom query is where multiple Axiom queries update the same set of rows. The AQ# tags for each query are on the same row.

In this case, the drill results will be only for whichever query is listed first on the Control Sheet. For example, if AQ1 and AQ2 update the same set of rows, then the drill results will be for AQ1. Any data for AQ2 will be ignored. Results depend on how the queries are set up; some configurations may provide useful drill results, while others will not.

NOTE: If the first query context is invalid for drilling (for example, if AQ1 is disabled or Drillable has been set to Off), then Axiom Software will attempt to drill on the next relevant query—in this example, AQ2.

Update-only Axiom queries

If the refresh behavior for an Axiom query is set to update-only, then that query is not required to have a defined calc method. If no calc method is defined, then the drill results will only present the data from the query itself—any formatting or formulas on the row will not be part of the drill.

Axiom queries with GetData functions in calc method

GetData functions can be used in the calc method of Axiom queries. In this case, the drilling context is the Axiom query, not the GetData functions. For more information about drilling behavior in "mixed" configurations, see Design considerations for drilling down GetData functions.
Axiom queries with GetData functions in field definition If a GetData function is used in the field definition of the Axiom query, it can only be drilled if the function does not use any cross-sheet references. If cross-sheet references are present, the function will return an error on the drill sheet. This applies if the GetData function is in the field definition directly or if the field definition uses a formula that references the GetData function in another cell.
Multiple data tables

If the query uses multiple data tables:

  • The hierarchies shown on the Drill Down menu are based on the primary table. Only hierarchies for shared lookup tables are valid for drilling in this circumstance.
  • The All Detail drill option will only be available if the tables share the exact same key columns and all of the key columns are lookup columns.
  • The Choose Columns dialog will only show columns from lookup tables.

System tables

Axiom queries to system tables (such as Axiom.Aliases) cannot be drilled.

Alternate aggregations

Use of AxAggregate on a column does not prevent drilling, but in some cases it does not return the values that you might expect on the drill sheet. For example, if AxAggregate(Avg) is used, the average values on the drill sheet will not correspond with the average value on the original row. This is because the sum by on the drill sheet is at a different level, resulting in different average calculations per record.

Data Conversions

If data conversion is enabled for the Axiom query, the conversion also applies to the drill down data.

Segmented data

Segmented data is not supported for use with drill-down drilling.