AX2216

Working with templates

Templates are the source documents for plan files. When working with templates, you set up features that are intended for eventual use in plan files, not within the template itself.

For example, most templates use Axiom queries to bring in data for each individual plan file. This Axiom query is only intended to be used within the resulting plan files, not within the template. The template itself should be kept clean of any department-specific data, so that it does not get accidentally left in the file and then copied into every individual plan file. To test template setup, you should create plan files based on the template and perform all feature testing in those plan files.

Note the following feature behavior differences when working in templates:

Axiom queries that are set to refresh on open do not refresh when the template is opened. Additionally, formulas do not refresh automatically when the template is opened.
Sheet protection, sheet visibility, and freeze panes settings as defined on the Control Sheet are not applied when the template is opened. This is intended to make it easier for you to work in these sheets without the added step of removing protection, unhiding, etc. All of these settings will be applied as expected in the resulting plan files (including workbook protection).
The Control Sheet is visible while working in a template. When plan files are built from the template, the Control Sheet is always hidden by default in the resulting plan files. You do not have to hide the Control Sheet in order to hide it in plan files.
Action codes are not processed automatically within templates. If you want to test the action code setup for a template, it is recommended to build a plan file and test it there. However, if you must test the setup within the template, you can use the Process command on the Axiom Designer ribbon tab to process action codes within a template.

Editing template workbook structure

Templates can have as many sheets as needed to meet the planning goals of the file group. Sheets can be used for various purposes, such as:

Instructions sheets to communicate instructions and contact information to end users
Summary sheets displaying summarized data, for information purposes
Planning sheets where end users input planning data
Work sheets to support features such as drop-down lists on other sheets, or to hold notes and side calculations for end users

You can add and remove template sheets as needed using standard spreadsheet functionality. Keep in mind the following:

If a new sheet will use a calc method library, give that sheet a unique name unless it is intended to share a calc method library with sheets in other templates in the file group. For example, if one template has a sheet named Capital, do not use that name for a sheet in another template unless those sheets will have the exact same structure and should share the calc method library.

If a sheet does not use calc methods, then it can share the same name across templates. For example, all templates can have a sheet named Instructions, and that sheet can be structured differently in various templates, as long as the Instructions sheet does not use calc methods.
Don't change the name of a sheet if calc methods exist for that sheet. Calc method libraries are stored based on sheet name. If you change the name of a sheet that has calc methods, that sheet will no longer have access to the calc methods.

If this occurs, you can restore access to the calc methods by renaming the sheet back to its original name. If you must rename the sheet, you will need to bring all calc methods into the sheet first, rename the sheet, then save all calc methods back to the new library.

Workbook protection is always applied to plan files. When plan files are created based on the template, end users will be unable to delete sheets or add new sheets.

Design considerations for template sheets

NOTE: This section assumes that you are using spreadsheet plan files. If you are designing form-enabled plan files, please see Axiom forms for general information on designing forms, as well as Using Axiom forms for planning.

Template sheets are free-format. You can structure the sheets however you like, but most templates are structured as follows:

Work rows at the top of the template to hold Axiom row controls (such as save-to-database and Axiom queries), as well as other calculations and information that end users do not need to interact with. This area is typically "hidden" from end users by using the freeze panes setting on the Control Sheet.
Work columns to the immediate left and to the far right, to hold Axiom column controls (such as save-to-database, Axiom queries, and calc method insertion controls). The work columns to the left are typically "hidden" from end users by using the freeze panes setting on the control sheet. The work columns to the right are placed past any columns that end users work with.
The main planning area where users review the plan and complete planning inputs. This section usually contains a header area that is locked onscreen via freeze panes, with information such as the relevant plan code and column titles. The planning rows may be hard-coded (rows that are defined in the template and copied into all plan files built from the template), or variable (rows that are brought in via Axiom query, specific to the current plan code).

Protecting sheets

Most template sheets are protected so that when plan files are created users can only make changes in designated cells. This sheet protection must be configured on the Control Sheet—do not manually apply protection to the sheet. Although the sheet protection is not applied while working in the template, the protection will be applied in the resulting plan files.

Hiding sheets

When plan files are created based on the template, all sheets in the template will be visible to end users except those that are explicitly hidden (and the Control Sheet, which is always hidden in plan files). If the template has work sheets that users do not need to interact with, those sheets should be configured as hidden on the Control Sheet. Although these configured sheets will not be hidden while working in the template, they will be hidden in the resulting plan files.

Reserved rows and columns

Rows 1 and 2 of a template sheet are reserved for template validation. You should not manually place anything in these rows or modify any existing contents. Template design should start at row 3. For more information, see Template validation.

NOTE: Template validation controls are optional, but we strongly recommend that you use them, to help ensure the integrity of your data, and to provide enhanced features for template and calc method management. However, if you have templates where you have placed content in row 1, then Axiom will not attempt to enforce the validation controls, and the template will still work.

Changing the column structure of a template sheet

If a template sheet uses calc methods, then the column structure of the sheet is interdependent with:

The associated calc method library for the sheet name
Other template sheets that use the same sheet name (within the file group)

Because of these dependencies, you should be very careful when editing the column structure of a sheet in a template. If calc methods have already been created for a particular sheet, then inserting or deleting a column may cause a mismatch between the sheet structure and the associated calc method library.

Additionally, if other templates in the file group share the same sheet name, the sheets in those templates must have the same sheet structure, so that the calc method library is in synch with all related sheets.

Ideally, you should finalize sheet structure as much as possible before saving calc methods to the library, or before copying the template to make additional templates. If you need to make a change to the sheet structure once calc methods have been saved, you should:

Bring all existing calc methods into the template sheet before making any structure changes. Calc method administrators can use File Options > Add Row(s) > Insert Calc Method(s) to select all calc methods and insert them at once.
Make the sheet structure changes, including related changes to the calc methods as appropriate.
Use Advanced > CM Library > Replace Multiple Calc Methods to update all calc methods for the changes.

NOTE: At this point, if you are using template validation, you will be warned that a column layout change has been made to the template. Don't worry about the warning—you are in the process of resolving the issue and you will dismiss it after making all changes. Continue with the calc method replacement.
If other templates in the file group use the same sheet name, then make the same sheet structure changes to those templates. (You do not need to do anything with the calc methods in this case, because the calc methods have already been updated for the structure changes.)
Once all updates have been made to the calc method library and to all impacted templates, reset the column layout data: Advanced > CM Library > Reset Column Layout Data. This only applies if you are using template validation.

Editing templates after plan files have been created

Generally speaking, you should not edit a template once plan files have been created with that template, unless you plan to rebuild the plan files. Once plan files have been created, any further changes to the template are not copied to existing plan files.

Some plan file utilities look to the source template in order to provide a list of choices for performing actions on plan files. In this case, the choices in the template must match the choices in the plan files. Because of this:

You should not delete, rename, or otherwise edit Axiom queries in the template once plan files have been created, because the Process Plan Files utility uses the source template to present the list of Axiom queries to process.
You should not delete, rename, or otherwise edit print views in the template once plan files have been created, because the Print Plan Files utility uses the source template to present the list of views to print.

However, if the file group is an on-demand file group, then you might want to edit a template even though plan files exist, so that the change can apply to new plan files created for the file group going forward. In this case it might not matter that the older plan files do not have the change. Alternatively you could create a new template to incorporate the change, and then edit the template assignments for the on-demand file group so that the new template is used going forward.

NOTE: This restriction does not apply when using virtual plan files with a file group, because each time a plan file is accessed, it is automatically rebuilt using the latest copy of the template. However, in this case you must be careful when making changes to templates, because if a user accesses a plan file while you are in the middle of making the change, the plan file will be built using this "in progress" copy. Instead you should make your change in a test copy of the template, verify that the change works as expected, then import the new copy of the template over the old copy (to preserve the template name and ID).