Design considerations for drilling down GetData functions

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

GetData design considerations

The following design considerations apply when setting up GetData reports for drilling:

Configuration	Design Consideration
GetDatas within formulas	Whenever possible, you should construct the report so that all GetData formulas within a row are not part of a larger formula, so that the row will be eligible for drilling. If you need to perform math or other manipulations on the result of a GetData function, you can work around it by placing the GetData function by itself in one cell on the row, and then referencing that cell within the full formula. This way the data will still be drillable. However, if the GetData function is nested within a larger function, the row will become ineligible for drilling.
Freeze panes and drillable row contents	If freeze panes is set for the sheet (on the Control Sheet), then the freeze panes settings will be copied and adapted to the drill sheet. Any content "hidden" to the left of the freeze panes will be brought to the drill sheet, but will remain hidden. If freeze panes are not set on the sheet, then the entire contents of the row are displayed on the drill sheet. If you have content to the left of the data that you do not want on the drill sheet, you should configure freeze panes settings for the sheet.
Multiple data tables	If the functions in a row query multiple data tables, then 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. Additionally, the Choose Columns dialog will only show columns from the shared lookup tables.
Excel Precision	If Microsoft Excel's Set precision as displayed feature is enabled for the workbook, then the GetData functions cannot be drilled if the cell precision is set to a different level than the stored value. Axiom Software will interpret the value difference as if the GetData function were within a formula, which is not a supported drilling configuration.

Drilling behavior for certain GetData row configurations

Note the following drilling behavior with certain GetData row configurations:

Configuration	Drilling Behavior
GetDatas with different filter criteria statements	If the GetData functions in the row have different filter criteria statements, then no "drill path" will be presented on the drill sheet. In this case, each GetData function has a different source filter and therefore a different drill path applies to each column. NOTE: All of the GetData functions might use the same filter criteria statement within the formula, but still have different effective filters due to use of sheet filters. This occurs when: The GetData functions query different tables, and the sheet filter applies to some tables but not others. Some of the GetData functions on the row use the "ignore sheet filter" parameter.
Alternate aggregations	Use of alternate aggregations in GetData functions does not prevent drilling down a row, however, any GetData functions with alternate aggregations are omitted from the drill results.
Data conversion	If all of the GetData functions in the row use the same conversion target, then that conversion target is applied to the drill down data. If the GetData functions use different conversion targets, or if some of the GetData functions have no conversion target, then no conversion is applied to the drill down data. Axiom Software informs you of this condition when you initiate the drill, and asks if you want to continue.

Configuration

Drilling Behavior

GetDatas with different filter criteria statements

If the GetData functions in the row have different filter criteria statements, then no "drill path" will be presented on the drill sheet. In this case, each GetData function has a different source filter and therefore a different drill path applies to each column.

NOTE: All of the GetData functions might use the same filter criteria statement within the formula, but still have different effective filters due to use of sheet filters. This occurs when:

The GetData functions query different tables, and the sheet filter applies to some tables but not others.
Some of the GetData functions on the row use the "ignore sheet filter" parameter.

Alternate aggregations

Use of alternate aggregations in GetData functions does not prevent drilling down a row, however, any GetData functions with alternate aggregations are omitted from the drill results.

Data conversion

If all of the GetData functions in the row use the same conversion target, then that conversion target is applied to the drill down data.

If the GetData functions use different conversion targets, or if some of the GetData functions have no conversion target, then no conversion is applied to the drill down data. Axiom Software informs you of this condition when you initiate the drill, and asks if you want to continue.

Drilling behavior for "mixed" report configurations

A "mixed" report configuration is one where GetData functions are used within an Axiom query. In this case, the drilling context is the "parent" Axiom query, not the GetData functions.

Depending on the setup of the query and the GetData functions, drill results may not be as desired for mixed configurations. Specifically, report configurations that use an Axiom query as a means to build out blocks of GetData functions may not provide the desired drill results, and in some cases may be essentially undrillable unless you configure the Axiom query so that it is no longer eligible for drilling.

For example, you might have an Axiom query that is set up to query the DEPT table. The in-sheet calc method is a multi-row block of GetData functions and other formulas. When the query is run, it creates a block for each department in the table. The resulting GetData functions cannot be drilled, because the drill context is the parent Axiom query, not the GetData functions. The parent Axiom query is already at the lowest level of detail for its primary table, and therefore cannot be drilled.

If the parent Axiom query were at a higher level of detail—say, DEPT.Region—then the Axiom query could be drilled down to DEPT. If you drilled the data in this case, the drill sheet would contain the block of GetData functions for each department in the region that was drilled. However, this may not be what you wanted when you drilled. The rows in each block could be summed at an ACCT.Category level, and perhaps what you wanted was to drill a specific GetData row to the account-level detail. This level of GetData drilling is only possible if you do one of the following:

Disable the Axiom query (Activate set to Off). In this case you would not be able to refresh the Axiom query unless you re-enabled it. However, if the only purpose of the query was to build out the GetData blocks, it might be acceptable to disable the query.
Disable drilling for the Axiom query (Drilling set to Off). In this case you could still refresh the Axiom query, but you could not drill it.

In both cases, now that the Axiom query is no longer eligible for drilling, Axiom Software will check to see if another drilling context is available. You could now drill the GetData functions directly, as long as they are otherwise eligible for drilling, according to the GetData drilling requirements.

NOTE: If the query is a horizontal query, then you can always drill the GetData functions within that query. Horizontal queries are always ineligible for drill down, so by default the drilling context is the GetData functions.

Get more information and training resources: www.kaufmanhall.com

Was this topic helpful?

drill_down_getdata_designconsiderations.htm