AX1416

Design considerations for master sheets

Master sheets are sheet "templates" that end users can copy to create one or more additional sheets in a file. These sheets must be specially designed for use in this environment. Keep in mind the following design considerations when creating sheets that you intend to designate as master sheets.

Control sheet settings and master sheets

When a user adds a new sheet to a file using a master sheet, all Control Sheet settings from the master sheet are copied to the new sheet, with the following exceptions:

The Sheet Visible/Hidden setting for the new sheet is automatically set to Visible.
The name of the master sheet is automatically written to the Master Sheet setting for the new sheet.

This copying of Control Sheet settings means that you can define Axiom file features on the master sheet and have those features automatically apply to the copied sheets—such as freeze panes settings, Axiom query settings, and save-to-database settings. If you need any of these settings to be different on the master sheet versus the copied sheets, you can use a formula that looks to the Master Sheet setting. This setting will be blank for the master sheet, but for copied sheets it will contain the name of the master sheet.

When a copied sheet is inserted into the file, sheet protection and default views are applied at that point (if configured on the Control Sheet). Other "on open" settings are not applied (but will be applied the next time the file is opened).

Visibility of the master sheet

In most cases, the visibility of the master sheet should be set to Hidden, using the Sheet Visible/Hidden setting on the Control Sheet (in the Sheet Options section). This assumes that the only purpose of the master sheet is to serve as a "template" for added sheets, and end users do not need to see and work with the master sheet itself.

If the master sheet is hidden, then the master sheet itself will not be visible in the file. However, when a user chooses to add a new sheet using the master sheet, the copied sheet will be automatically flagged as visible.

If you choose to leave the master sheet visible, and if end users are able to make any edits to the master sheet, then those edits will be copied over to any new sheets added using that master sheet. When using master sheets with templates/plan files, keep in mind that copied sheets are made from the master sheet in the plan file, not from the master sheet in the source template. Therefore if the master sheet is visible and editable in plan files, over time the master sheet may no longer be the same in each plan file.

Calc method libraries and master sheets

For files that support calc method libraries, those libraries can be used with master sheets. All copied sheets created from the master sheet will use the same calc method library as the master sheet. This means that:

Axiom queries in copied sheets can use the calc method library.
End users can insert and change calc methods in copied sheets, subject to the normal controls (security permissions and calc method controls).
The Apply Calc Method Changes utility can be used to update calc methods in copied sheets.

This association between copied sheets and the original calc method library is enabled by use of the Master Sheet setting on the copied sheets. When a user adds a new sheet using a master sheet, the name of the master sheet is automatically written into this setting. This associates the copied sheet with its source master sheet, and allows the copied sheet to use the same calc method library as the master sheet. Whenever any calc method operations take place on a copied sheet, the master sheet's calc method library is used.

Copied sheets must maintain the same column structure as the original master sheet and its calc method library. End users should not be able to insert or delete columns in the master sheet or any copied sheets when using a calc method library.

Axiom queries and master sheets

When setting up Axiom queries on master sheets, keep in mind the following:

If Axiom queries on the master sheet are enabled, then those queries will be run when the file is manually refreshed. This may cause the master sheet to be populated with unwanted data that will then be copied to any future sheets created from that master sheet. You may want to dynamically disable the queries on the master sheet but enable them on the copied sheets by using a formula that looks to the Master Sheet setting.

For example, you could use a formula like the following in the Active setting for the query:

=IF(F35="","Off","On")

This would check the Master Sheet cell and turn the query off if it is blank (for the master sheet), but turn it on otherwise (for the copied sheets).
If an Axiom query is set to refresh on open, that query will not run when a copied sheet is first inserted into the plan file. However, it will be run the next time the file is opened.
Process Plan Files cannot be used to execute Axiom queries on copied sheets. Because the list of Axiom queries is based on the source templates for the selected plan files, the copied sheets and their Axiom queries are not available. The master sheet and its queries can be available for processing, but selecting queries on the master sheet will not cause the corresponding queries on the copied sheets to be run. You may want to disable Refresh during document processing for the Axiom queries on the master sheet so that they are not available for execution in Process Plan Files.

Save-to-database and master sheets

When setting up save-to-database processes on master sheets, keep in mind the following:

Assuming that the master sheet is being used as a template only, you should set up the save-to-database so that it is only enabled for the copied sheets, not the master sheet. You can do this by using a formula that looks to the Master Sheet setting.

For example, you could use a formula like the following in the Enabled setting for the save-to-database process:

=IF(F35="","Off","On")

This would check the Master Sheet cell and turn the save-to-database off if it is blank (for the master sheet), but turn it on otherwise (for the copied sheets).
Make sure that the keys on each copied sheet are unique, so that data is not overwritten as the save-to-database for each sheet is processed sequentially. You may need a Detail key in the target table in order to make each sheet's data unique, where the Detail text includes a reference to the current sheet.
Make sure that the save-to-database process on each copied sheet uses a unique zero tag, to avoid zeroing out data from other save-to-database processes in the file. You may want to use the current sheet name in the zero tag.

Printing and master sheets

Copied sheets can only be printed when end users are printing within the file using the Print button. The Print Plan Files option cannot be used to print copied sheets, because the list of sheets to print is built from the source template for the selected plan files. The master sheet will display on the list, but selecting the master sheet will not cause the copied sheets to be printed (and if the master sheet is not visible in the individual plan files, it will not be printed even if selected).