Home >

AX2311

How the "drill-down" drill sheet is created

When you initiate a drill down in a spreadsheet Axiom file, Axiom Software creates a new, temporary spreadsheet that contains a drill sheet with the drilling results.

Drill data

On the drill sheet, the drill data is returned by creating an Axiom query based on the settings in the original sheet.

If you are drilling an Axiom query, Axiom Software creates the drill query as follows:

• The field definition row and the in-sheet calc method are copied from the original sheet to the drill sheet. This includes any column filters or other special syntax applied to the field definitions.

  NOTE: If the Axiom query uses a calc method library instead of an in-sheet calc method, then the calc method applied to the row being drilled is copied to the drill sheet. For more information, see Design considerations for drilling down Axiom queries.

• Axiom Software identifies the "sum by" level of the original Axiom query and then finds the associated entries in the field definition row. These entries are replaced by entries for the selected drill level, and for a Description column (if the drill level is a key column and a description column is available). The selected drill level is added to the "sum by" for the drill query.

• A data filter is created for the drill query that limits the data returned to the row that was drilled. The data filter is determined based on the keys in the data control column of the original query. If the original query had a data filter, that filter is also appended to the data filter for the drill query.

If you are drilling a GetData row, Axiom Software creates the drill query as follows:

• A field definition row is created that contains entries for the selected drill level, and for the columns used in each GetData function on the row.
• An in-sheet calc method is created based on the formats and formulas used in the row. Note that non-number cell formatting applied to the first cell in the drillable row will also be applied to the cells that display the drill level.
• The selected drill level is applied as the "sum by" level for the query.
• The query data is filtered in one of the following ways:
  • If all of the GetData functions in the row use the same filter criteria statement, this is applied as a data filter for the entire query.
  • If the GetData functions use different filter criteria statements, these are applied as column filters to each field definition. Additionally, all of the filters used in the row will be concatenated using OR and applied as a data filter, in order to limit the overall data query to only the applicable data.

In both cases, if a sheet filter was defined on the original sheet, that filter is also applied to the drill sheet. This also applies to any Quick Filter currently applied to the original file. Note that the "behind the scenes" Quick Filter on the original file is converted to an explicit sheet filter on the drill sheet, so the GetCurrentValue function will return "None" for the drill sheet.

Headers

Headers are created on the drill sheet as follows:

• If the original sheet has freeze panes settings defined on the Control Sheet, the entire frozen area (including rows "hidden" above the freeze panes point) is copied to the drill sheet and becomes the header area. It is assumed that the frozen section contains titles and column headers for the report that would also apply to the drilled data.
• If the original sheet does not have freeze panes settings, then Axiom Software inserts a basic header row that contains the column names for the data being returned. Note that if you have columns that are calculations instead of database data (like a variance column), no column title will be placed on these columns.
• In all cases, Axiom Software places a column title over the drill target column, to identify the items resulting from the drill. If you drilled to a key column (for example, DEPT), and the associated reference table has a designated Description column, then the Description column is also automatically displayed and titled.
• Axiom Software inserts a row in the header to display the drill path. If headers were copied over from the original sheet, this row displays underneath them. Otherwise, this row displays above the automatically-generated header row.

Frozen panes

Frozen panes are set on the drill sheet as follows:

• If the original sheet has freeze panes settings defined on the Control Sheet, those freeze panes are applied to the drill sheet, after adjusting for inserted rows and columns.
• If the original sheet does not have freeze panes settings, then Axiom Software automatically applies freeze panes to the left and top of the first data column.

NOTE: Axiom Software takes into account the available screen area and will only freeze columns up to approximately half of the screen width. If the inherited or automatic freeze panes settings would exceed this width, then columns will not be frozen. Columns to the left of the drill column will be hidden instead.

Subtotals

Subtotals are placed in the drill sheet as follows, depending on whether you are drilling an Axiom query or a GetData row.

If you are drilling an Axiom query, subtotals are created as follows:

• If both the in-sheet calc method and the field definition for the query are single rows, then Axiom Software adds a subtotal row below the drill data. If either of these settings use multiple rows, no subtotal row is created in the drill sheet.
• The subtotal row starts as a copy of the in-sheet calc method, so that any existing formulas in the row are retained (such as a variance calculation). Then, for each non-key numeric column in the drill data, Axiom Software adds a SUM formula to the subtotal row.

If you are drilling a GetData row, subtotals are created as follows:

• Any column that queries a numeric column from a data table has a subtotal.
• If a column uses a non-GetData formula, that formula is copied to the subtotal row.

"Numeric" in this context means any column with a data type of Numeric or Integer (all types).

Views

If a sheet view was applied to the original sheet, the same view will be applied to the drill sheet if the View tag is defined within the header area and the header area is enclosed in frozen panes.

If the View tag is not defined within the header area or if freeze panes is not set, then the view will not be applied to the drill sheet. This may result in additional content on the drill sheet that is not visible on the original sheet, if the view is being used to hide rows or columns.