AX2324

Using dependent refresh variables

You can make one refresh variable dependent on the selected value of another refresh variable. If the dependent variable is configured with dynamic settings—such as using formulas to determine the list or column or filter—then the dependent variable can change based on the selected value of the "parent" variable. You can also determine whether the dependent variable displays at all, by making the [IsEnabled] property for the variable dynamic.

Dependent variables work as follows:

  • When the variables are presented to the user in the refresh dialog or filter panel, the parent variable is initially available and the dependent variable is not. The dependent variable may be visible but grayed out, or it may not display at all (depending on whether the dependent variable is enabled initially).

  • When the user specifies a value for the parent variable, that value is placed in the selected value cell for that variable, and the file is calculated.

  • The settings for the dependent variable are re-read, and the refresh dialog or filter panel is updated for any changes. The user can now select a value for the dependent refresh variable, assuming the dependent variable is enabled.

This behavior applies in both environments—the Refresh Variables dialog in the Desktop ClientGeneral term for using either the Excel Client or the Windows Client, both of which are installed to the user's desktop. and the Filter panel in the Web Client.

Configuring a variable as dependent

If you want a variable to be dependent on another variable, set the [DependsOn] property for the dependent variable to the name of the "parent" variable. For example, if the Account variable is dependent on the value selected for the Category variable, then enter Category in the [DependsOn] property for the Account variable.

Once a variable has been designated as dependent on another variable, then you can use formulas in its variable settings to dynamically change the variable based on the parent variable selection. For example, you could use formulas to:

  • Specify whether the variable is enabled or not (see the following visibility discussion for more information)
  • Change the list choices for the variable
  • Change the Table.Column for the variable
  • Change the filter applied to the column

NOTE: The variable name cannot be dynamic; it must remain static because it is used to identify the variable. If you need the name of the dependent variable to change based on the selection of the parent variable, then you must make the display name dynamic instead of the name.

In all of these cases, the formula should look to the selected value of the parent variable and change in response. For example, users could specify a numeric value for the parent variable, and then the dependent variable could be enabled or not depending on whether the numeric value was greater than a certain amount. Or users could select column names from the parent variable (such as Dept, VP, or Region), and then the column value for the dependent variable could change based on the parent variable selection.

The calculation and update of the refresh dialog or filter panel only occurs if a variable is flagged as dependent using the [DependsOn] property, and only dependent variables are updated. If you use formulas in variable settings but the variable is not flagged as dependent, then the variable will not update once the refresh dialog or filter panel has been presented to the user.

Visibility of dependent variables in the refresh dialog or filter panel

When setting up dependent variables, you should consider whether you want the dependent variable to be visible in the refresh dialog or filter panel from the start, or whether it should be hidden initially. Dependent variables behave as follows, based on the value of the [IsEnabled] property:

  • If the dependent variable is enabled, then the dependent variable displays in the refresh dialog or filter panel, but it is grayed out and unavailable to the user. Once the user selects a value for the parent variable, the dependent variable becomes available for use.

  • If the dependent variable is not enabled, then the dependent variable is omitted from the refresh dialog or filter panel. Once the user selects a value for the parent variable, the dependent variable may or may not become visible in the refresh dialog or filter panel, depending on the formula that was used in the enabled setting.

    For example, the formula might be set up so that once any value is set for the parent variable, the dependent variable becomes enabled. Or the formula might be set up so that the dependent variable only becomes enabled if the parent variable value meets a certain criteria.

Dependent variable example

A common dependency example is to use one variable to define a filter for a second variable. In this example, the Category variable (the parent variable) is used to select an account category. The Account variable (the dependent variable) then presents a list of accounts, filtered by the selected category.

To filter the Account variable by the value selected for the Category variable, the [ColumnFilter] cell for the Account variable contains a formula that looks to the [SelectedValue] cell for the Category variable. Right now the filter cell is blank because no category has yet been selected.

When the user refreshes the file in the Desktop Client, the refresh dialog appears as follows:

The dependent variable, Account, is initially grayed out, and the user must define the Category variable first. Once the user selects a category, that category is placed in the sheet (in the [SelectedValue] cell in the data source) and then the file is calculated. This process occurs in the background and is transparent to the user.

The Account variable is now available to be selected. When the user clicks on Choose Value, the list of accounts is filtered based on category (for example, Revenue), like so:

The user's account selection is placed in the sheet, and the file is refreshed.

The following is an example of how the variable values and filter creation could look in the worksheet once all variables have been selected. In this example, we are taking the final account selection and creating a filter to apply to an Axiom query.

Design considerations

When configuring dependent variables, you should consider the following:

  • Within the RefreshVariables data source, the parent variable should be placed before the dependent variable, because variables are presented to users in the order they are found in the data source. If the parent variable is located after the dependent variable, then the variable selections may not make sense to the user as they are presented in the refresh dialog or filter panel.

  • The parent variable can be required or not as appropriate. If the parent variable is not required and no selection is made for that variable, then the dependent variable will never become active and the user will not be able to make a selection for that variable. If you need the user to make a selection for the dependent variable, then both the parent variable and dependent variable should be required.

If you want to use a CheckBox variable as a parent variable, so that the dependent variable changes based on whether the check box is selected or not, then you must do one of the following:

  • Configure the CheckBox variable as required, and define a default value for the variable in the [SelectedValue] field. This is to ensure that the variable always has a selected value of True or False.

  • Otherwise, make sure that your formulas are set up to account for the blank case and treat it the same way as an entry of False.