AX2761

Drilling data: Using Drill Down

You can drill down a row in an Axiom file to view the data at a different level of detail. For example, if a row of data in the report shows budget totals for the Northwest region, you can drill the row to see the values for each individual department in that region. This type of drilling is known as drilling down.

You can use drilling down in report files or plan files, but the most typical use case is in reports. In plan files, most data is already at the lowest level of detail, so drilling down may only be useful if the plan file has a summary sheet with drillable data.

If a row is eligible for drilling, you can drill down hierarchies that were set up for the data. For example, your system may have a Geography hierarchy such as: Country > Region > DEPT. You can also drill directly to the bottom of the data (all dimensions) or drill using any related column in the data.

You can drill data rows that result from an Axiom query or that are built using GetData functions. A few limitations apply, and some advanced configurations have special behaviors. For more details, see the topics linked in the See Also box.

You can disable drilling on a per sheet level. If drilling is disabled for a sheet, the Drill option is disabled while you are on that sheet. Consider taking this action if the data on the sheet is not conducive to drilling.

To drill down a row of data:

  1. Place your cursor in the row of data to drill.

    If you drill a row of data that is part of a multiple-row calc method,place your cursor anywhere in the multiple-row calc method. The drill results will be for all rows of the calc method.

  2. On the Axiom tab, in the File Options group, click Drill, and then select the applicable drill level from the Drill Down sub-menu.

    NOTE: In system with installed products, this option may be located on the Main tab.

    Drill option Description

    Hierarchies

    Your system may have defined hierarchies that outline logical drilling paths. For example, you may have a Geography hierarchy that allows you to drill from Country to Region to individual departments.

    Hierarchies are defined per dimension (on the relevant reference table), and are specific to your system.

    On the Drill menu, hierarchies are listed first if defined. Only hierarchies relating to the current data are shown.

    NOTES:  

    • If the data to be drilled comes from multiple data tables, only the hierarchies from shared lookup reference tables are available.

    • If you drill an Axiom query, hierarchy options are disabled on the menu if you are already at that level of detail. For example, if the report is at the VP level already, VP is disabled on the menu. However, when you drill GetData functions, all hierarchy options are present on the menu, because the GetData functions may all have different sum by levels.

    All Detail

    Selecting All Detail takes you directly to the bottom of the data. Essentially, you are drilling based on all dimensions at the same time, rather than on one specific dimension.

    The drill sheet contains one column for each dimension (key column) in the data, including a description column for each (if applicable).

    NOTE: If the row contains data from more than one data table, this option is available only if the tables share the same key columns and all of the key columns are lookup columns.

    Choose Columns

    Select Choose Columns to drill based on any relevant column for the current data, including non-lookup key columns.

    In the Select Columns dialog, select the column (or columns) to drill by. You can select from any column in the primary data table, as well as any column in lookup reference tables.

    This drilling option is free-format. No validation occurs to determine if a specific column selection makes sense in relation to the current data.

    NOTES:  

    • Calculated fields are not displayed and cannot be used for drilling.
    • If the row contains data from multiple data tables, you can only select columns from shared lookup reference tables.

A temporary file Drill_Filename.xlsx is opened. This file contains a drill sheet named Drill_DrillLevel that shows the results of the selected drill. The top of the drill sheet displays your current drill context.

To continue drillingl, you can do either of the following:

  • Return to the original sheet (or a different sheet) in the original file, and then select a new drill level. If you left the temporary drill file open, a new drill sheet is added to that file, with the new drill results. If you closed the temporary file, Axiom creates a new temporary file.
  • Select a row in the drill sheet and continue drilling to a lower level of detail. A new drill sheet is created in the temporary file with the results.

When you are finished viewing the drill results, close the temporary file. While it is possible to use Save As to save the temporary file, this process is often not useful. You can perform the drill again at any time to view the results. If you perform the same drill multiple times, consider creating a new report that displays the data at the applicable level. To share the drill results with someone else, create a snapshot copy of the temporary drill file.

Double-click drilling

If Axiom double-click actions are enabled for the sheet, you can drill a row of data by double-clicking it. In this case, a dialog displays the drilling options for the current row selection. Select the applicable drill level, and then click OK.

NOTE: Certain double-click actions may take priority over drilling. See Double-click behavior.

See also