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."

Drilling down can be used 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 have been set up for the data. For example, your system may have a Geography hierarchy such as: Country > Region > DEPT. You can also choose to 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.

Drilling can be disabled on a per sheet level. If drilling has been disabled for a sheet, then the Drill option is disabled while you are on that sheet. This may be done 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 that you want to drill.

    If you are drilling a row of data that is part of a multiple-row calc method, then you can 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 desired 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, then only the hierarchies from shared lookup reference tables are available.

    • If you are drilling an Axiom query, hierarchy options will be grayed out 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 grayed out on the menu. However, when you are drilling 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 very "bottom" of the data. Essentially, you are drilling based on all dimensions at once, rather than on one specific dimension.

    The drill sheet will contain 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, then this option is only available if the tables share the exact same key columns and all of the key columns are lookup columns.

    Choose Columns

    Selecting Choose Columns allows you 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) that you want 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 entirely free-format. There is no validation to determine if a particular column selection makes sense in relation to the current data.

    NOTES:  

    • Calculated fields do not display and cannot be used for drilling.
    • If the row contains data from multiple data tables, then only columns from shared lookup reference tables can be selected.

A temporary file opens, named Drill_Filename.xlsx. 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.

If you want to continue to drill, 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, then a new drill sheet will be added to that file, with the new drill results. If you closed the temporary file, then Axiom Budgeting & Performance Reporting 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 will be 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, typically this is not useful. You can always perform the drill again at any time to see the results. If you find yourself performing the same drill over and over, you may want to create a new report that displays the data at the desired level. If you want to share the drill results with someone else, the best approach is to create a snapshot copy of the temporary drill file.

Double-click drilling

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

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

See also