Defining calc method variables using a CalcMethodVariables data source

You can define calc method variables using a CalcMethodVariables data source. This provides a dynamic method of defining calc method variables, because formulas can be used in the sheet to enable or disable variables, or to change variable configuration.

In order to use a CalcMethodVariables data source with a calc method, you must:

How the data source works

When a user inserts or changes a calc method, and Variables Use Data Source is enabled for the calc method, then Axiom checks the file for the presence of a CalcMethodVariables data source with a matching sheet name and calc method name. For example, if the calc method library is for sheet Budget and the calc method is named Insert Only, then Axiom looks for the following tag: [CalcMethodVariables;Budget;Insert Only].

If a matching data source is found, then it is used to present calc method variables to the user. If a matching data source is not found, then the calc method is inserted without variables. The lack of a matching data source is not an error condition.

Because the data source is defined in the file and evaluated at the time of calc method insertion, it can use formulas to change dynamically. You can dynamically enable or disable certain variables (or the entire data source), and you can change the configuration of variables. For example, formulas can be used to determine the filter to apply to a Grid or ComboBox variable, to filter the list of available column values.

The CalcMethodVariables data source is very similar to the RefreshVariables data source, and supports the same set of variables. However, note the following differences:

  • Default value: Calc method variables do not support defining default values. The user must specify a value for the variable, or else it is treated as not defined. If you do not want users to leave the variable blank, then it should be configured as required.

  • Selected value: When the calc method is inserted into the sheet, the user's selected values for the variables are not placed in the [SelectedValue] column of the data source. Instead, they are placed in a designated location within the calc method, as defined by the [RelativeLocation] property.

    However, if you have configured a variable as a dependent variable, the [SelectedValue] column is still used to temporarily write back the selected value of the parent variable. This is done so that formulas can resolve and the dependent variable can update based on the selected value for the parent variable. This process happens in the background during the calc method insertion, and does not affect the file.

Multiple files in a file group can use the same calc method library, if those file share the same sheet name. When using a data source to define calc method variables, the data source must be defined in each file that uses the library, because the variables are not stored in a centralized location. For example, if you have two templates with a Budget sheet that use the Budget calc method library, each template must contain a copy of the data source. This could potentially be an advantage, if you want to use a different variable configuration in each template. However, if both templates use the same variable configuration, then you must set up and maintain the data source in both templates. In this case, if you do not need the additional variable types or features provided by the data source, you may find it easier to use the legacy variables in the calc method properties.

NOTES:  

  • If a CalcMethodVariables data source contains dependent variables, and the calc method insertion occurs in the Desktop Client, then the [SelectedValue] column does not get cleared after the insertion occurs. This is a known issue that is intended to be fixed in a later patch or release. It is not intended to persist values in the [SelectedValue] column.

  • If you are inserting a calc method in an Axiom form, and the associated CalcMethodVariables data source has a value in the [SelectedValue] column, that value will be populated into the Calc Method Variables dialog (but only on initial insertion of the calc method). This is a known issue that is intended to be fixed in a later patch or release. It is not intended to support default values in the CalcMethodVariables data source.

Defining the CalcMethodVariables data source

The CalcMethodVariables data source must be defined in the file where calc methods will be inserted. For templates/plan files, the data source is defined in the template used to create the plan files.

The data source can be defined on any non-control sheet of the file. In practice, the data source is typically placed on a designated sheet named something like Variables. If the file is a standard Axiom spreadsheet file that users will access in the Excel Client or Windows Client, then this sheet is typically hidden from end users (though it does not have to be).

To create a CalcMethodVariables data source:

  • Right-click the cell in which you want to start the data source, then select Axiom Wizards > Insert Calc Method Variable Data Source.

    This option is only available in files that belong to a file group. Only file groups can use calc method libraries.

The wizard adds the primary tag, all column tags, and one row tag to define a single variable. To create variables, you can manually populate the variable properties.

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. For more information, see Using formulas with Axiom feature tags.

The following screenshot shows an example CalcMethodVariables data source with two variables:

The CalcMethodVariables data source uses the following syntax:

Primary tag

[CalcMethodVariables;SheetName;CalcMethodName]

The SheetName identifies the sheet associated with the calc method library. This is the sheet where the calc method is inserted.

The CalcMethodName identifies the calc method for the variables. This is the name of the calc method as defined in the calc method properties.

For example, if the calc method is named Edit Months and the calc method library is for the Budget sheet, then the primary tag should be renamed as follows:

[CalcMethodVariables;Budget;Edit Months]

The placement of this primary tag defines the control column and the control row for the data source.

  • All column tags must be placed in this row, to the right of the tag.
  • All row tags must be placed in this column, below the tag.

Row tags

[Variable]

Each row flagged with this tag defines a unique calc method variable. Generally speaking, each variable represents a value that you want the user to input or select.

Column tags

Each column in the data source defines a variable property, such as the variable name and type, and whether the variable is enabled. All variables share a set of general properties. Additionally, certain variables have additional properties that only apply to that particular variable type.

[VariableType]

This column defines the variable type. The variable type determines the valid property columns for the variable.

The following variable types are most commonly used for calc method variables:

  • Calendar: The user can select a date from a calendar.

  • ComboBox: The user can select a value from a drop-down list. The list can be generated based on a specified table column, or an Axiom query, or a ComboBox data source. The user can type into the box to filter the items in the list.

  • Decimal: The user can enter any numeric value, including decimals.

  • Grid: The user can select a value from a specified table column (for example, ACCT.ACCT to select from a list of accounts). The column values are presented in a searchable grid dialog. Multi-select can be enabled.

  • GUID: This is a special variable type that is not presented to the user. Instead, a globally unique identifier is automatically generated and placed in the calc method.

  • Integer: The user can enter any whole number.

  • RadioButton: The user can select a single value from among two or more radio buttons. The list of radio buttons can be generated based on a specified table column, or an Axiom query, or a ComboBox data source.

  • RelatedColumnValue: This is a special variable type that is not presented to the user. Instead, it is used to return a related column value for a parent variable, and place that value in the calc method. For example, if the user is selecting an account code, this can be used to place the account description in the calc method.

  • String: The user can enter any string value.

  • StringList: The user can select any item in a manually defined list.

The following additional variable types are technically supported for use in calc method variables, but typically they are not useful in this context. If you want to use one of these variables, see the variable information as documented for refresh variables.

  • AdvancedFilter: The user can create a filter criteria statement using the Advanced Filter view, or create a limit query statement.

  • CheckBox: The user can select or clear a check box.

  • HierarchyFilter: The user can select one or more values from a defined hierarchy to result in a filter criteria statement. Note the following limitations when using this as a calc method variable as opposed to a refresh variable:

    • The property [UseAsQuickFilter] does not apply to calc method variables and is not present in the CalcMethodVariables data source.
    • This variable does not currently work in the Web Client when used as a calc method variable.

  • RangeSlider: The user can select the top and bottom values within a defined range, using slider buttons.

  • Slider: The user can select a single value within a defined range, using a slider button.

Note the following about placement of the data source tags:

  • Column tags can be in any order. Optional column tags can be omitted from the data source if they are not being used. For example, if none of your variables are StringList type, you can omit the [ListChoices] tag.

  • Column and row tags do not have to be continuous. Axiom will continue searching the control row and control column for valid tags until it reaches a bracketed tag that does not belong to the data source.

Testing calc method variables

When the file is saved, certain calc method variable settings in the data source are validated and will cause an error if invalid. After completing the variable set up, you should save the file and correct any errors found.

NOTE: Axiom does not validate the sheet name and the calc method variable name in the primary CalcMethodVariables tag. If your variables are not being recognized when the calc method is inserted, you should make sure that Variables Use Data Source is enabled in the calc method properties, and verify that the correct sheet name and calc method name are used in the primary data source tag.

Once you have set up the file as desired and corrected any invalid settings, you should test the calc method variables by inserting the calc method into the sheet, using the same method that you intend end users to use. Make sure that:

  • The variable name and selections make sense from a user perspective. If it does not seem clear what you are asking the user to do, you may want to edit the variable name or use a different variable type.

  • If the variable is not required, test a blank entry to make sure that the calc method still works as expected if no value is specified. If not, you may want to edit the calc method design or make the variable required.

  • If you are using dependent variables, make sure that any dynamic settings are working as expected. Try entering different values for the parent variable to make sure you have accounted for all possibilities.