AX2159
When defining variables for a calc method, you can define them within the calc method properties. This is the "legacy" approach to defining calc method variables. When using this approach, the variables are stored within the centralized calc method library along with the actual rows of the calc methods. All files with access to the calc method library also have access to the variables.
Legacy calc method variables support the following variable types:
-
String: The user can enter any string value.
-
Integer: The user can enter any whole number.
-
Decimal: The user can enter any numeric value, including decimals.
-
List: The user can select any item in a defined list.
-
Grid: The user can select any item from a specified table column (for example, ACCT.ACCT to select from a list of accounts).
-
Related Column Value: This variable type is not presented to the user. It is used to return a related column value for a parent Grid variable, so that the value can be referenced within the calc method.
-
GUID: This variable type is not presented to the user. It generates a globally unique identifier (GUID) and places it into the designated location when the calc method is inserted.
Other than related column values, legacy calc method variables do not support any other type of dependency between variables. Additionally, legacy calc method variables have a limited ability to read information from the file where they are to be inserted, using cell references. For example, when setting up a Grid variable, you can use a cell reference to read a filter from the file, to filter the list of values in the column.
To define legacy variables for a calc method:
You can define legacy variables when you are creating a new calc method or editing an existing calc method. The following procedure assumes that you are editing an existing calc method. The same user interface is available when you are creating a new calc method.
-
Optional. Insert the calc method into the sheet and then select the relevant rows (or select an instance of the calc method that is already in the sheet).
This step is not required, but having the calc method selected in the sheet allows Axiom to show you exactly where the variable value will be inserted into the calc method. (When creating a new calc method, you would have selected the applicable rows anyway as part of the creation process.)
- On the Axiom tab, in the Advanced group, select CM Library > Manage Calc Methods.
-
In the Manage Calc Methods dialog, select the calc method and then click Edit.
The Edit Calc Method dialog opens, displaying the settings for the selected calc method.
-
In the Calc method variables section, click Add variable
.A new variable row is added to the grid.
-
In the Name box, type a name for the variable. A cell reference can also be used, enclosed in brackets—for example,
[A5].The name displays in the Calc Method Variables dialog, and tells the user what value is being specified. The name can be just a term (“Account”) or it can be an instructive phrase (“Select an account”).
-
In the Type box, select the type of variable.
-
String: The user can enter any string value.
-
Integer: The user can enter any whole number.
-
Decimal: The user can enter any numeric value, including decimals.
-
List: The user can select any item in a defined list.
-
Grid: The user can select any item from a specified table column (for example, ACCT.ACCT to select from a list of accounts).
-
Related Column Value: This variable type is not presented to the user. It is used to return a related column value for a parent Grid variable, so that the value can be referenced within the calc method.
-
GUID: This variable type is not presented to the user. It generates a globally unique identifier (GUID) and places it into the designated location when the calc method is inserted.
-
-
If the variable type is List or Grid, define the list of allowed variable values.
List: Define the list values
To define the list of allowed values for a List variable, click the [...] button to the right of the type selection.
In the List Items dialog, type each item on a separate row. To create a new row, use the
Enterkey.Cell references can also be used to define list items. Each cell reference must be placed on its own row, enclosed in brackets.
Example list definition
Grid: Select the source column
To specify the column to use for the Grid variable, click the [...] button in the Column Name field.
The Select Value Column dialog opens.
-
In the left-hand side of the dialog, select the column. You can change the table view and filter the list to find the appropriate column. Only columns from reference tables or document reference tables can be used.
By default, the user can select any value in this column. If desired, you can define a filter to limit the list of values. In the Column Filter box, type a filter criteria statement, or click the Filter Wizard button
to create one. A cell reference can also be used, enclosed in brackets—for example, [A5]. -
In the right-hand side of the dialog, select any additional columns that you want to present to users when they select a value.
NOTE: This option is only available when the selected column is a key column. If the column is a grouping column, then no additional columns can be shown. When the user selects a value, the list will be the list of unique values in the grouping column.
Example column selection
-
-
In the Relative Location box, enter the location where the variable value will be placed, relative within the calc method.
The location is specified as follows:
<ColumnLetter><CalcMethodRow>.For example:
-
If the value should be placed in column B of the calc method, within the first row of the calc method, enter the following:
B1. If the calc method is a single-row calc method, the calc method row is always 1. -
If the value should be placed in column B of the calc method, but within the third row of the calc method, enter the following:
B3.
The entry for the calc method row must be valid within the context of the current calc method. For example, if the current calc method is a three-row calc method, then the only valid entries for the row are 1, 2, and 3. If you attempt to specify B4 for the three-row calc method, the entry is invalid.
If the calc method rows are selected in the sheet (as described in step 1), then you can click Show location within the current spreadsheet selection
, and the cursor will move to the location where the value would be placed. -
-
If you want the user to be required to specify a value for the variable, select the Required check box.
If the variable is required, then users must specify a value in order to insert the calc method or use it to overwrite another.
If the variable is not required, then users can leave the variable blank, resulting in the corresponding cell being blank when the calc method is inserted. If the calc method is overwriting another calc method, the original cell contents are retained if the variable is left blank.
You cannot specify a default value for a variable; either the user specifies a value, or the cell is left blank.
-
If the variable should only display when the calc method is inserted by a user, select the Insert Only check box.
Some variables, such as selecting an account for the row, should only be displayed when the calc method is inserted. When a user changes an existing row using the calc method, the existing account should be retained.
If Insert Only is selected, then the variable will not display when a user performs an overwrite with the calc method, or when a user double-clicks an existing row in the sheet to edit the variable values. If Insert Only is not selected, then the variable will display in all cases.
If all of the variables for the calc method are set to Insert Only, then the Calc Method Variables dialog will only display when the calc method is inserted.
-
Repeat steps 4-10 to define any additional variables for the calc method.
When the Calc Method Variables dialog is displayed to the user, variables are listed in the order that they are listed in this grid. You can change the order of variables by selecting a variable row and then clicking the Move variable uparrow or the Move variable down arrow.
NOTE: The presence of a Related Column Value variable restricts how variables can be moved up and down, because the Related Column Value variable must be underneath its parent Grid variable. If you have a Grid / Related Column Value pair, and you want to change their order in the list of variables, then you must move the other variables up or down in the list instead of attempting to move the pair.
If you want to delete a variable, select the variable row in the grid and then click Remove variable
. - Click Apply to save your changes to the calc method (or OK if you are finished editing the calc method).
Sorting behavior for column values
If the variable uses a column value, the list of values is sorted based on the source column. In some cases the values may not be displayed as expected:
-
If the column is from a driver table, the values will most likely not be displayed in the order they are stored in the driver file. When the values are saved to the database from the driver file, they are sorted based on the key column of the table (column A of the driver sheet).
-
If the column is a string column that contains numeric values (values that are all numbers and values that start with numbers), Axiom will attempt to sort these values in numeric order. Values that start with letters will then be sorted in alphabetical order.
Using cell references with legacy calc method variables
Several properties of legacy calc method variables support use of cell references. Instead of "hard-coding" a value within the calc method properties, you can point to a cell location in the file. This cell location can contain regular text, or it can use a formula to dynamically derive a value, or it can contain a range that Axiom will use to dynamically generate a list of choices.
The following properties support cell references:
- The variable name
- If the variable type is List, the list items
- If the variable type is Column Value, the column filter
To use a cell reference, enter the reference into the property like so:
[A5]
or
[Sheetname!A5]
Cell references are always absolute. An entry of [A5] always points to cell A5, regardless of the placement of the inserted calc method or the target cell for the variable. However, you can use a formula in cell A5 to dynamically change the contents—for example, depending on the current insertion point location. The workbook is calculated before the Calc Method Variables dialog opens.
For list items only, cell ranges can also be used.
| Variable Property | Cell Reference Support |
|---|---|
|
Variable name |
Enter the cell reference into the Name field. When the Calc Method Variables dialog is presented to the user, the name of the variable will be derived from the referenced cell. |
|
List items |
Enter the cell reference into the List items dialog on its own line, just as you would if it was a hard-coded value. The list can contain multiple cell references and/or values, the results of which will all be joined to create the final list. For list items only, the referenced cell can contain a range, such as If desired, you can also enter the range directly within the List items dialog. In this case, use brackets:
Example list items with cell reference In this example, the contents of cell A5 will be combined with the hard-coded item "Corporate" to create the full list of choices. If cell A5 contains a range, then the values in that range are added to the list. |
| Column filter (Grid only) |
Enter the cell reference into the Column Filter box when selecting the column to use for the list. The referenced cell must contain a full, valid filter. If you are using a cell reference for the column filter, the only entry in the Column Filter box must be the cell reference. For example, you cannot use a "mixed" configuration where part of the filter is defined in the box and the remaining part is read from the cell reference.
Example column filter with cell reference In this example, the column filter is being read from cell C3. If cell C3 contains |
