AX2027

Creating the drill target report for custom drilling

When a custom drill is executed, Axiom Software takes the parameter values from the Drilling Control Sheet of the source file, and passes them to the designated locations on the target output file. If a parameter has more than one possible value, the user is prompted to select the value to use.

The drill target report must be configured in a way to intelligently use these passed parameters to query data from the database. For example, if a filter criteria statement is passed, you can reference the target cell as the data filter for an Axiom query. If dimensionality is passed (for example, Dept.Region), you can reference the target cell as the sum level for an Axiom query.

The format of the drill target report is completely flexible. The target report can replicate the data columns of the source file, but be configured to use the passed filter and dimensionality to present data at a different level. Or you can add or remove data columns, or use a completely different set of data columns. The target report is essentially a brand new report, with no restrictions.

Because custom drilling is such a flexible feature, an understanding of advanced report writing concepts is necessary to set up the drilling parameters on the source sheet and to set up the target report to properly use the passed parameters.

NOTE: The Axiom queries in the drill target file do not need to be configured to refresh on open. When the output file is opened via custom drilling, the file is automatically refreshed after the drilling parameters are placed in the file.

Controlling access to the drill target report

Users must have at least read-only access to the drill target report, or else the drill will not execute because the file cannot be opened.

You may want to place your drill target files in reserved locations in the Reports Library, so that you can easily grant access to users who need it. When granting access, you can clear the Show in Explorer option, which means that the drill target report will be hidden when the user browses the Explorer task pane or similar "Explorer views." However, as long as the user has Read Only access, they will still be able to open the report when executing the custom drill.

If a user has read/write access to the drill target report, then the file will open with that level of access when drilling. You can suppress this behavior by enabling Downgrade to read-only on open on the target report's Control Sheet.

Lastly, if you don't want read-only users to be prompted to save the file when they close it, you can enable Close read-only files without prompting to save on the target report's Control Sheet. This provides a more streamline drilling experience as the user can drill and close repeatedly without needing to dismiss extraneous save prompts.

Using the same file as the drill source and drill target

You can set up custom drilling so that the drill target report and the source file are the same report. In this case, the Report folder and the Report file to Open are set to the same folder path and file name as the source file. The Sheet to activate is the sheet in the source file where the drill results are presented.

The Reuse drill target setting determines the behavior when a user initiates the drill:

  • If Off (the default behavior), then a read-only copy of the source file is opened (with a number appended to the file name), and the drill results are presented in this copy. If the user drills the source file again without closing the drill copy, then another copy is opened to show the results of that drill.

  • If On, then the source file is closed and then reopened to display the drill results. Each subsequent drill closes and reopens the source.

    NOTE: In this configuration, if the source file contains unsaved changes the user will not be prompted to save them before the file is closed and then reopened.