Calc method control tag syntax
This section provides a detailed explanation of the calc method control tag syntax, and several examples.
Standard insertion control syntax (InsertCMColumn)
The control column syntax for standard calc method controls is as follows:
[InsertCMColumn; HeaderLabel]
The InsertCMColumn portion of the tag is required, and flags the column as the control column.
HeaderLabel is the text to appear on the Add Row(s) > Custom Inserts menu, for this set of enabled rows. The header text could be the name of the sheet, or it could describe the allowed actions. For example:
[InsertCMColumn; Add Rows to Budget Sheet][InsertCMColumn; Add New Employees]
The header label parameter is optional and may be omitted if not necessary. For example, if you are enabling a calc method control column for the sole purpose of enabling rows for overwriting, then no insertion points will display on the menu anyway, and you can omit the header text.
NOTES:
- The primary tag must be placed in the first 500 rows of the sheet.
- Formulas can be used to create the tags, as long as the initial bracket and identifying keyword are whole within the formula.
Dynamic insertion control syntax (DynamicCMColumn)
The control column syntax for dynamic insertion controls is as follows:
(missing or bad snippet)(missing or bad snippet)Examples of DynamicCMColumn tags
[DynamicCMColumn; Update an account;InsertHere; acct.acct.description.category|acct.category NOT IN ('balance sheet','statistics')|Budget!BB22|F1]
This example places the text "Update an account" on the Add Row(s) > Custom Inserts menu. When a user clicks on this item, a dialog opens so that the user can select an account. In addition to the list of accounts, the Description column and the Category column also display in this dialog. The list of accounts is filtered to not include accounts that belong to the Balance Sheet and Statistics categories.
After the user selects an account, the selected item is placed into cell BB22 of the Budget sheet, and the sheet is calculated. The active row for dynamic insertion is then identified by looking for the first row in the column that contains an InsertCM tag with the matching dynamic marker text of "InsertHere". If a calc method is inserted as a result of the dynamic insertion process, then the selected account is placed in the first row of the inserted calc method, in column F.
[DynamicCMColumn; Update an account; ; acct.acct||Budget!BB22]
This example is the same as the prior example, except that only the required parameters have been specified. This is provided to contrast the difference in behavior when certain optional parameters are omitted. In this case:
- No dynamic marker is used, so the active row will be determined by locating the first row in the column that contains an InsertCM tag.
- No additional columns are specified for the table, so the selection dialog will use the "simple view" (account codes with the description column appended in parentheses).
- No filter is specified for the table, so all accounts will be listed in the selection dialog.
- No calc method destination is specified, so the selected item will not be placed in any inserted calc method.
[DynamicCMColumn; Add a new account; InsertHere; acct.acct.description.category| |Budget!BB22|F1;MultipleInsert]
This example uses the MultipleInsert parameter to allow calc method insertion at multiple locations, not just the first matching location. The user would select an account and that account might be inserted at one or more locations in the sheet (in this example, the plan file might contain several departments so multiple instances of the same account would make sense).
Enabled row syntax (InsertCM)
The enabled row syntax for calc method controls (standard and dynamic) is as follows:
[InsertCM; InsertionPointLabel; CalcMethodList; GroupList; AllowedAction; InsertLocation; MaxInsertCount; DynamicMarker]
The InsertCM portion of the tag is required, and marks the row as enabled for a calc method action. The allowed actions, allowed calc methods, and navigation depend on the optional parameters:
| Parameter | Description |
|---|---|
InsertionPointLabel | Defines the text to display on the Add Row(s) menu of custom insertion points, organized under the header text of the control column. NOTE: This parameter only applies when using standard calc method controls (InsertCMColumn). It is ignored when using dynamic insertion controls (DynamicCMColumn), but must still be delimited with an empty semicolon. This parameter only applies to rows that are enabled for insert (either insert only, or insert and overwrite). When an insertion point is selected from the menu, the action is always to insert a calc method at that location. If omitted, then this insertion point does not display on the Add Row(s) menu, but users can still insert calc methods manually at this location (if the row is enabled for insert). Custom insertion points cannot be used to navigate users to a location for overwriting. If the only action allowed on the row is overwriting, then this parameter is ignored. |
CalcMethodList | Defines the list of allowed calc methods for the row. Separate multiple calc method names with commas. If a calc method list is defined, then only calc methods that match this list can be used to insert or overwrite at this location (unless a group list is also defined). If both the CalcMethodList and GroupList are omitted, then any calc method can be used to insert or overwrite at this location. |
GroupList | Defines the list of allowed calc method groups for the row. Separate multiple group names with commas. If a group list is defined, then only calc methods that belong to the specified groups can be used to insert or overwrite at this location (unless a calc method list is also defined). |
AllowedAction | Specifies the allowed action for this location. The action can be one of the following:
|
InsertLocation | Specifies where a calc method will be inserted relative to the current row, if the row is enabled for insertion:
|
MaxInsertCount | Specifies the maximum number of calc method instances that can be inserted at this location, if the row is enabled for insertion. For example, if the user is inserting detail rows, you may want to give the user the option to insert multiple detail rows at a time. The behavior of this option depends on how many calc methods are available for insert at this location: (missing or bad snippet) |
DynamicMarker | This item is only applicable when used in a dynamic insertion control column that has a dynamic marker defined in the DynamicCMColumn tag. After a user selects the desired account (or other dimension) and the sheet is calculated, the "active" row for insertion is determined as the first row in the column with a matching dynamic marker. Use of a dynamic marker is optional. If no dynamic marker is specified in the DynamicCMColumn tag, the active row is determined as the first row in the column with an InsertCM tag. |
The CalcMethodList and GroupList are combined together to result in the full list of calc methods allowed at the location. You can use one or the other, or both. Note the following:
- If only one calc method is allowed, that calc method is automatically inserted or used to overwrite when the user performs the calc method action on the row. The calc method dialog does not display to the user.
- If multiple calc methods are allowed, then the calc method dialog is filtered to only show the allowed calc methods.
If a parameter is omitted, and you want to specify a parameter after that parameter, you must delimit the omitted parameter with a semicolon.
NOTES:
If you want calc method actions to be available on inserted calc method rows within a sheet (whether inserted via Axiom query or by a user), then the calc method itself must be designed so that the InsertCM tag is saved as part of the calc method.
If the calc method is a multiple-row calc method, and you want to allow users to change it, it is not necessary to tag all rows with an InsertCM tag. Typically, only the first row is marked with an InsertCM tag and enabled for change calc method, but any tagged row within the calc method will trigger the ability to change the calc method.
The InsertCM tag can be placed within a formula if desired. If you are using dynamic insertion, it is essentially a requirement (so that the tags can adjust dynamically based on the user's dimension selection). In this case, you may want to use the right-click helper dialog to construct the initial tag, and then modify the tag to convert it to a formula. Once the tag is within a formula, you will be unable to make further modifications using the right-click dialog.
Examples for use with a standard calc method control column (InsertCMColumn)
[InsertCM;]
In this example, users can manually insert or overwrite using any calc method at this location. Because no insertion point label is defined, no text appears on the Add Row(s) menu for this location. Note that if you omit all other parameters, you must include the first delimiting semicolon.
[InsertCM; Insert New Employee]
In this example, users can manually insert or overwrite using any calc method at this location. Users can also insert at this location by selecting the "Insert New Employee" insertion point from the Add Row(s) menu.
[InsertCM; Insert New Employee; Add New Salaried Employee, Add New Hourly Employee; ; Insert]
In this example, users can insert one of the two specified calc methods at this location. The GroupList parameter is not used, and is delimited with an "empty" semicolon. Users can only insert calc methods at this location, as specified by the AllowedAction parameter. Overwriting is not allowed.
[InsertCM; Add Employees; ; Payroll]
In this example, users can insert or overwrite using any calc method that belongs to the Payroll group. The CalcMethodList parameter is not used, and is delimited with an "empty" semicolon.
[InsertCM; ; Input Monthly; Budget ;Change]
In this example, users can change calc methods using the Input Monthly calc method, or using any calc method that belongs to the Budget group. Because users cannot insert at this location, the InsertionPointLabel parameter is not used, and is delimited with an "empty" semicolon. In order to change the calc method, a user must manually place the cursor at this location and select Add Row(s) > Change Calc Method.
[InsertCM; Insert New Employee; New Employee; ; Insert; Above; 5]
In this example, only one calc method, New Employee, is allowed for insertion at this location. When the user selects this insertion point, the specified calc method is automatically inserted at the location. The Insert Calc Method dialog does not display (even if the user is an administrator). However, this example also uses the MaxInsertCount parameter, so the user will first be prompted to specify how many instances of the calc method to insert (up to 5).
Examples for use with a dynamic insertion control column (DynamicCMColumn)
In the following examples, the current row is being identified as the active insertion row by the presence of the DynamicMarker text "InsertHere" in the final parameter. This assumes that the DynamicCMColumn contains the same DynamicMarker text. If the DynamicCMColumn does not use a dynamic marker, then the described actions would only apply if the row is identified as the active row by being the first row marked with InsertCM in the column.
[InsertCM; ; Budget Detail; ; Insert; Below; 0; InsertHere]
In this example, the row is enabled for insert, and only the Budget Detail calc method is eligible for insert, so the user will not be prompted to select a calc method. The MaxInsertCount parameter is used so that the user will be prompted to specify how many items to insert; since the number is 0, the user can insert an unlimited number of detail rows. Finally, since the InsertLocation is specified as below, the rows will be inserted below the current row (instead of the default behavior of above).
[InsertCM; ; ; ; GoTo; ; ; InsertHere]
In this example, the AllowedAction parameter is set to GoTo, so the user is taken to the current row and no calc method is inserted. Since calc method insertion is not possible on this row, the CalcMethodList, GroupList, InsertLocation, and MaxInsertCount parameters are irrelevant and therefore left blank. This type of tag is intended for use on existing rows, so that if a user selects a dimension item that already exists in the sheet, they are simply taken to the existing row.
[InsertCM; ; ; BudgetGroup; Change; ; ; InsertHere]
This example is similar to the previous example, except in this case the AllowedAction is Change instead of GoTo. The dynamic insertion behavior is the same: the user is taken to the current row and no calc method is inserted. However, if the user has Allow Calc Method Change permissions, they have the additional option of changing the existing row with another calc method (in this case, any calc method that belongs to the BudgetGroup). The user must explicitly go to the Add Row(s) menu if they want to perform the change; the dynamic insertion process does not open the Change Calc Method dialog or prompt the user in any way.
