AX3660

Managing drill-through definitions

If you want to enable drill-through drilling in Axiom files, then you must define a drill-through definition for each target table / source sequence that you want to be able to drill.

For example, if you want to be able to drill through CYA data (current year actuals) to detailed transactional data, then you must define a drill-through definition for the GLDetail table that maps to the CYA column sequence (M1-M12 in the GL2021 table).

You can create, edit, and delete drill-through definitions by using the Drill-Through Manager. Only administrators or users with the Administer Tables security permission can access this dialog.

Adding a drill-through definition

New drill-through definitions can be added at any time. Users can drill through data for the associated sequence as soon as the definition has been saved.

Before you can create a drill-through definition, the following components must already be in place:

• The desired target table must already be created in the database.
• The desired source table must already be created in the database, and the columns that you want to drill must be part of a column sequence defined on that table. In most cases, these column sequences already exist for other purposes (such as for calculated fields), so you do not need to create them specially for drill-through definitions. If you do need to create a new sequence, see Column sequences.

Only administrators and users with the Administer Tables permission can add a drill-through definition.

To create a new drill-through definition:

1. On the Axiom tab, in the Administration group, click Tables > Table Administration > Drill-Through Manager.

2. In the Drill-Through Manager dialog, click New .

3. In the Number of Columns dialog, type the number of columns for this drill-through definition, and then click OK.

   This number must correspond to the number of columns in the sequences that you want to map to. For example, if this definition is intended to allow users to drill CYA data (current year actuals), and that sequence is 12 columns, then the definition must have 12 columns.

   NOTE: Once you specify the number of columns for the definition, it cannot be changed.

   The Drill-Through Definition Editor opens.

4. In the General Details section, complete the following items:

   Item	Description
   Name	The name of the drill-through definition. This name will display to users on the Drill menu, so it should be brief and intuitive. For example, if the definition defines a drill to transaction-level detail, the name could be "Transactions" or "Transaction Detail".
   Target Table	The name of the table that provides the drill-through data. For example, actuals data may be stored in a table named GL2021, and you want to be able to drill that table to the transaction data held in the GLDetail table. In this case GLDetail is the target table.
   Description	Optional. A description for the drill-through definition. The description only displays in the Drill-Through Manager.

5. In the Mapped Sequences section, specify the associated column sequences for this definition. These sequences define the columns that users can drill in Axiom files.

   1. Click Edit Mapped Sequences.

   2. In the Select Sequences dialog, add the column sequences that you want to map to this definition.

      For example, if this drill-through definition is to enable drilling on actuals data from the GL2021 table, you would select the 12-month sequence defined for that table (most likely named "M" or "Months").

      To find the desired column sequences, you can filter the list by typing into the filter box, or you can change the view to display tables by table name, table type, or folder.

      This dialog displays any table that has a column sequence that matches the number of columns for the drill-through definition. For example, if the drill-through definition has 12 columns, you can select any column sequence that has 12 literal columns or 12 effective columns.

      "Effective columns" takes into account the start period of the sequence. For example, if the sequence has 6 columns but the start period is 7, that sequence has 12 effective columns because the sequence covers periods 7-12. When you drill on the "first" column of the sequence, it will return data based on the column 7 filter in the drill definition.

      You can select multiple sequences, but each sequence must be able to use the same column filter to determine the appropriate drill data in the target table.

   3. Click OK to return to the Drill-Through Definition Editor.

   NOTE: Although this section allows you to specify multiple sequences, in most cases each drill-through definition will only have one selected sequence. Each drill-through definition has one set of column filters to determine the appropriate drill data in the target table, so if you do specify multiple sequences, they must be able to use the same column filters. This will only apply in certain rare cases.

6. In the Column Filters section, specify the drill filter to associate with each column in the mapped sequence.

   This is the filter to apply to the target table when drilling on a column from the mapped sequence. For example, if you are drilling on column 1 of the mapped sequence (M1 or CYA1), the filter to apply to the target table would be something like: YearMo=202101.

   You must specify a filter for each column. You can type the filter into the filter box, or you can use the filter wizard .

   IMPORTANT: The column filters determine the data that will be returned when performing "drill-through" drilling. It is important that the column filters correspond appropriately to the columns in the mapped sequences, or else drill results will not be as you expect.

7. In the Drill Target Columns section, specify the columns that you want to display on the drill sheet.

   1. Click Edit Drill Target Columns.

   2. In the Select Columns dialog, add the columns that you want to display on the drill sheet.

      You can select any column from the target table, and any column from a lookup table for the target table. For example, if the target table is GLDetail and uses key columns of DEPT and ACCT, then you can select any column from the GLDetail table, the DEPT table, and the ACCT table.

   3. Click OK to return to the Drill-Through Definition Editor.

   4. In the Drill Target Columns list, change the order of columns as desired.

      Columns will display in drill sheets in the order listed here. To move a column, select the column in the list and then use the arrows to move it up or down.

8. Click OK to save the definition.

Users can drill-through using the definition as soon as it is saved.

Cloning an existing drill-through definition

You can clone an existing drill-through definition to create a new definition, instead of creating the definition from scratch. Only administrators and users with the Administer Tables permission can clone a drill-through definition.

To clone a drill-through definition:

1. On the Axiom tab, in the Administration group, click Tables > Table Administration > Drill-Through Manager.

2. In the Drill-Through Manager dialog, select the definition that you want to clone, and then click Clone .

   The Drill-Through Definition Editor dialog opens, containing a copy of the cloned definition.

3. Edit the settings as desired for the new definition, and then click OK.

   For more information on the available settings, see Drill-through definition properties.

Editing a drill-through definition

Drill-through definitions can be edited at any time. Changes take effect immediately, meaning that any new drills initiated after the changes are saved will use the new settings.

NOTE: The number of columns for a drill-through definition cannot be changed. All other properties can be edited.

Only administrators and users with the Administer Tables permission can edit a drill-through definition.

To edit a drill-through definition:

1. On the Axiom tab, in the Administration group, click Tables > Table Administration > Drill-Through Manager.

2. In the Drill-Through Manager dialog, select the definition that you want to edit, and then click Edit.

3. In the Drill-Through Definition Editor dialog, edit the definition settings as desired.

   For more information on the available settings, see Drill-through definition properties.

   If you change the target table for the definition:

   • Any columns in the Drill Target Columns section will be cleared. You must select new columns that relate to the new target table.
   • Column filters are retained and validated against the new table. If the column filter is not valid in the context of the new table, the filters are highlighted in red and must be changed.
4. Click Apply or OK to save your changes.

Deleting a drill-through definition

You can delete a drill-through definition at any time. If the definition is deleted, that drill-through path will no longer be available to users in Axiom files.

Only administrators and users with the Administer Tables permission can delete a drill-through definition.

To delete a drill-through definition:

1. On the Axiom tab, in the Administration group, click Tables > Table Administration > Drill-Through Manager.
2. In the Drill-Through Manager dialog, select the definition that you want to delete, and then
   click Delete.
3. At the confirmation prompt, click Yes.

The drill-through definition is deleted.