AX2026

Drilling Control Sheet settings

The Drilling Control Sheet defines settings to drill a particular Axiom file using custom drilling. In the Drilling Control Sheet, you can define one or more drill options for each sheet in the file. For each sheet, you can specify one drill option as the "double-click" option, which means that drill will be executed automatically when a data row in the sheet is double-clicked. Otherwise, users execute drills from the Drill menu in the File Options group.

To add a Drilling Control Sheet to an Axiom file:

On the Axiom Designer tab, in the Advanced group, click Tools > Add a Control Sheet > Drilling. Only administrators have access to this command.

The Drilling Control Sheet is named Control_Drilling. This sheet is only visible to administrators and to users with the Allow Sheet Assistant permission. Otherwise, it is hidden by default.

The Drilling Control Sheet must be present and configured in the file that you want to drill. The target "drill output" report does not need a Drilling Control Sheet.

The top section of the Drilling Control Sheet contains general options that apply to all drills defined for the sheet, followed by several sections of defined drill options. The default Drilling Control Sheet contains settings for three drilling options per sheet.

The Drilling Control Sheet contains one column of settings, followed by several definition columns. To set up a sheet in the file for drilling, enter the name of the sheet into row 1 of the first open definition column. The sheet listed here must also be enabled for drilling on the main Control Sheet (Enable Drilling must be set to On).

Sample Drilling Formula

Row 4 of the Control Sheet contains a sample formula that can be used to obtain a value based on the active row in the report sheet. You can then reference this value in the Drill Option settings, to pass it to the target file.

The sample formula uses GetRowNumber("Active") to find the row number of the currently active row, and then returns the contents of column A on that row. You would need to set up your report sheet to place row-specific values into a particular column (such as a row-specific filter), and then modify the sample formula to point to that column.

It is not required to use this formula to set up your custom drill; the sample formula is provided as a convenience.

General Options

The general options at the top of the sheet relate to enabling "double-click" drilling for the sheet.

Field Description

Field	Description
Double-click to drill	Specifies whether users can drill by double-clicking cells in a report (On/Off). NOTE: It is not necessary to also enable Axiom Double-Click on the main Control Sheet.
Double-click default drill ID	Optional. If Double-click to drill is enabled, this setting specifies the drill option to execute when a user double-clicks a cell in a report. Enter the ID number of the option, such as `2` for Drill Option #2. If left blank, then the following occurs: If only one drill option is active, that drill option will be executed. If multiple drill options are active, then a drill dialog opens where the user can select which drill option to execute (as well as complete any selections for that option).
Reuse drill target	Specifies whether the drill target file is reused each time a drill is executed (On/Off). By default, this is set to Off. If you drill a report, leave the target file open, and then drill the report again, the second drill will open a read-only copy of the target file, with a number appended to the file name. If you set this to On, then the target file is reused each time you initiate a drill. If you drill a report, leave the target file open, and then drill the report again, the target file is automatically closed and then reopened for the new drill. When this option is enabled, you can only ever have one copy of the drill target open, displaying the results of the most recent drill. NOTE: The behavior of this setting is slightly different when the drill source and drill target are the same file. For more information, see Using the same file as the drill source and drill target.

Double-click to drill

Specifies whether users can drill by double-clicking cells in a report (On/Off).

NOTE: It is not necessary to also enable Axiom Double-Click on the main Control Sheet.

Double-click default drill ID

Optional. If Double-click to drill is enabled, this setting specifies the drill option to execute when a user double-clicks a cell in a report. Enter the ID number of the option, such as 2 for Drill Option #2.

If left blank, then the following occurs:

If only one drill option is active, that drill option will be executed.
If multiple drill options are active, then a drill dialog opens where the user can select which drill option to execute (as well as complete any selections for that option).

Reuse drill target

Specifies whether the drill target file is reused each time a drill is executed (On/Off).

By default, this is set to Off. If you drill a report, leave the target file open, and then drill the report again, the second drill will open a read-only copy of the target file, with a number appended to the file name.

If you set this to On, then the target file is reused each time you initiate a drill. If you drill a report, leave the target file open, and then drill the report again, the target file is automatically closed and then reopened for the new drill. When this option is enabled, you can only ever have one copy of the drill target open, displaying the results of the most recent drill.

NOTE: The behavior of this setting is slightly different when the drill source and drill target are the same file. For more information, see Using the same file as the drill source and drill target.

Drill Options

The following settings are available for each drill option. A drill option defines the target report for the drill, and the parameters to pass to the target report. Each enabled drill option defined for a sheet will be available from the Drill menu in the File Options group.

Field	Description
Name	The name of the drill. This name displays on the Drill menu when the drill is activated.
Activate	Specifies whether the drill is active. If On, users can select the drill from the Drill menu. If Off, the drill does not display on the Drill menu. If no drills are active for a sheet, then the Drill menu displays "No active drills found on worksheet name".

Field

Description

Name

The name of the drill. This name displays on the Drill menu when the drill is activated.

Activate

Specifies whether the drill is active. If On, users can select the drill from the Drill menu. If Off, the drill does not display on the Drill menu.

If no drills are active for a sheet, then the Drill menu displays "No active drills found on worksheet name".

Target File

This section specifies the target file for the drill option. The file must be a report file and must be located in the Reports Library. The user executing the drill must have at least read-only access rights to the target file, as defined by the user's file permissions in Security.

For more information, see Creating the drill target report for custom drilling.

Field	Description
Report folder	The folder where the target file is located. The Reports Library in the Axiom Software file system is assumed as the location, so only the folder name and any sub-folder names must be specified. For example: `Budget Reports` if the report is located in the Budget Reports folder `Budget Reports\Drill` if the report is located in the Drill sub-folder of the Budget Reports folder
Report file to Open	The name of the target file. The full file name must be entered, including the extension. The file must be an Axiom report file.
Sheet to activate	The name of the sheet that you want to be active when the file is opened. This is typically the sheet in the file where the drilling information is being passed from the source file, but it does not have to be.
Target not found error message (optional)	A custom message to display if the target file cannot be found. This is typically used to display a custom message when the user attempts to drill on a row that is not intended to be drilled. You would accomplish this by using a formula in the Report file to Open setting, so that the formula resolves to an invalid file name when the user is not on a row that is valid for drilling. NOTE: If the Report file to Open setting is blank, then no error displays and no action is taken when a user attempts to drill. The setting must resolve to an invalid file name in order to display this message.

Parameters

This section specifies the parameters for the drill option. Typically a drill passes at least two parameters to the output file—a parameter to define the filter for the drill, and a parameter to define the dimensionality for the drill. You can define additional parameters if necessary.

Each parameter can have one or more values. If a parameter has more than one value, then users will be prompted to select the desired value when the drill is executed.

Each parameter has the following settings:

Field Description

Parameter name

Field	Description
Parameter name	Name of the parameter. If the parameter has multiple values, this name also displays in the dialog where users select a value when drilling.
Cell address for chosen option	Defines the cell address, in the target output file, where you want the parameter value to be passed. The cell address must include the sheet name. For example: `Income_Statement_Drill!C7`. For example, if the parameter is for a filter, this specifies the cell in the target file where the filter will be placed. The target output file would be configured so that this cell is used to define the data in the report (for example as the data filter for an Axiom query).
Value	The value for the parameter. This is the actual value to be passed to the cell address in the target file. The value cell can contain a cell reference or a formula to derive the value based on the user's location in the report, or it can contain a literal value. Literal values are usually only used when the parameter has multiple values for a user to select. If the parameter is for a filter, the cell would likely contain a cell reference to a work area where the user's current context is derived. For example, if the user initiates the drill by double-clicking on a row that contains data for benefit accounts (ACCT.SummaryGroup='Benefits'), the work area would use functions such as Axiom Software's GetRowNumber and Excel's Index to derive the current row and obtain the criteria statement for the data in that row. The sample formula in row 4 of the Drilling Control Sheet can be adapted for this purpose. If the parameter is for dimensionality, the cell would likely contain a literal value for the user to select. For example, the parameter could have multiple values such as Dept, Dept.VP, and Dept.Region. When the user double-clicks on a row to initiate the drill, they can choose at which level to view the resulting data. If you need more value fields for a parameter, you can add more. See the following section for more details.

Name of the parameter. If the parameter has multiple values, this name also displays in the dialog where users select a value when drilling.

Cell address for chosen option

Defines the cell address, in the target output file, where you want the parameter value to be passed.

The cell address must include the sheet name. For example: Income_Statement_Drill!C7.

For example, if the parameter is for a filter, this specifies the cell in the target file where the filter will be placed. The target output file would be configured so that this cell is used to define the data in the report (for example as the data filter for an Axiom query).

Value

The value for the parameter. This is the actual value to be passed to the cell address in the target file.

The value cell can contain a cell reference or a formula to derive the value based on the user's location in the report, or it can contain a literal value. Literal values are usually only used when the parameter has multiple values for a user to select.

If the parameter is for a filter, the cell would likely contain a cell reference to a work area where the user's current context is derived. For example, if the user initiates the drill by double-clicking on a row that contains data for benefit accounts (ACCT.SummaryGroup='Benefits'), the work area would use functions such as Axiom Software's GetRowNumber and Excel's Index to derive the current row and obtain the criteria statement for the data in that row. The sample formula in row 4 of the Drilling Control Sheet can be adapted for this purpose.

If the parameter is for dimensionality, the cell would likely contain a literal value for the user to select. For example, the parameter could have multiple values such as Dept, Dept.VP, and Dept.Region. When the user double-clicks on a row to initiate the drill, they can choose at which level to view the resulting data.

If you need more value fields for a parameter, you can add more. See the following section for more details.

Adding more drill options or parameters

By default, the Drilling Control Sheet contains settings for three drill options. If you need more drill options, you can copy and paste an existing section.

Unfreeze panes on the Drilling Control Sheet so that you can see the drill tags in Column A.
Select all of the rows of an existing drill option, and then copy and paste them below the last drill option in the sheet. You will probably want to leave at least one blank row in between drill options, for ease of reading and reviewing the options.
In the first row of the new (pasted) drill option, in column A, edit the [StartDrill#] tag to reflect the next sequential number. For example, if the last drill option in the sheet (before you copied) is [StartDrill3], edit the tag of the new option to be [StartDrill4]. You should also edit the title in column C to match (i.e. "Drill Option #4").

You can now define the settings for the new drill option as appropriate.

The same concepts apply if you need more drilling parameters or parameter values within an existing drill option. Each parameter has a tag of [DrillStartParameter#] (where # is the number of the parameter within the drill option). You can copy and paste an existing parameter section to create a new one, and update the number in the tag.

Each parameter can have multiple parameter values, with a tag of [DrillParameterValue]. You can copy an existing value row within a parameter section.