AX1493

Executing data lookups

Data lookups can be processed in the following ways:

On refresh: If the DataLookup data source is unnamed, then it will be executed when the file is refreshed.
File open: You can specify one or more DataLookup data sources to execute when the file is opened.
After Axiom query: You can specify one or more DataLookup data sources to execute after a particular Axiom query is run.
On demand: You can expose the Execute Data Lookups command in a ribbon tab, task pane, or Axiom form, so that users can execute one or more DataLookup data sources on demand.

Additionally, you may want to set up your file so that the data lookup results are automatically cleared when the file is saved. This option is typically used in report files to help ensure that users do not see data saved in the file by other users. To do this, use the setting Clear DataLookups on save in the Workbook Options section of the Control Sheet.

Executing on refresh

By default, all unnamed DataLookup data sources are executed whenever the file is refreshed. This includes refreshes that are triggered manually by a user (by clicking the Refresh button), as well as refreshes that happen automatically (such as refreshing an Axiom query on open or after saving data).

Therefore, you should only leave the data source unnamed if you want this automatic and potentially frequent refresh behavior. If the data lookup truly only needs to be executed once per file session, or only when certain other events occur, then you should give the data source a name and configure it to run only when necessary.

When data lookups are executed on refresh, they are executed after Axiom queries are run. Therefore, if the data source is built out using an Axiom query, it will be updated as part of the refresh.

Executing when the file is opened

You can specify one or more DataLookup data sources to execute automatically when the file is opened. This is the typical approach when the DataLookup data sources are used to retrieve "static" values that are then referenced throughout the file.

To do this, use the setting DataLookups to run on open, located in the Workbook Options section of the Control Sheet.

You can list multiple data sources in a comma-separated list, in the order you want them to be executed. Note the following:

The list of data lookups can be qualified with sheet names, or unqualified. For example, if the data source is named Data, it can be listed as Sheet1!Data or just Data.
If data source names are qualified with sheet names, then Axiom Software will only scan the listed sheets for the specified data sources. If the data source names are unqualified, then Axiom Software must scan all sheets for the data sources, which can impact performance. It is recommended to use qualified data source names whenever possible.
The list of data lookups must be comprised of either all qualified names, or all unqualified names. Mixing qualified and unqualified names is not supported and will result in an error when the data lookups are executed.
If you want to run unnamed data sources on open, you can use SheetName![unnamed] to run all unnamed data sources on the specified sheet only, or [unnamed] to run all unnamed data sources on all sheets. Remember that if you are also running an Axiom query on open, then the unnamed data sources will automatically be executed as part of that refresh.

Alternatively, you can configure a DataLookup data source to execute on open by giving it the reserved name AxRefreshOnOpen. Data sources with this name will automatically execute on open, after any other data sources listed in the Control Sheet property. You can have multiple data sources with this reserved name, and assign them an execution order in the DataLookup tag as needed.

When data lookups are executed on file open, they are executed after functions are calculated, and before "refresh on open" Axiom queries are run. Therefore, if you are building out the DataLookup data source via Axiom query, you should not use this setting. Instead, you should configure the Axiom query to refresh on open, and then execute the data lookup after the Axiom query.

If you are refreshing on open in conjunction with refresh variables, keep in mind the following:

The data lookups listed in DataLookups to run on open are run before refresh variables are processed. You should use this option if the refresh variable configuration depends on data returned by the data lookup.
Data lookups that use the reserved name AxRefreshOnOpen are run after refresh variables are processed. You should use this option if the data lookup configuration depends on the user's refresh variable selections.

NOTE: When a file is opened using Open Without Refresh, the DataLookups to run on open setting is ignored. However, data lookups using the reserved name AxRefreshOnOpen are still run.

Executing after an Axiom query

For each Axiom query, you can specify one or more DataLookup data sources to execute automatically after the Axiom query is run. This option is intended to support executing named DataLookup data sources that are being constructed dynamically via Axiom query. The Axiom query builds out the data source rows, and then when the Axiom query is finished, the data lookups are executed.

To do this, use the setting DataLookups to run, located in the Refresh Behavior section of the Axiom query settings on the Control Sheet.

You can list multiple data sources in a comma-separated list, in the order you want them to be executed. Note the following:

The list of data lookups can be qualified with sheet names, or unqualified. For example, if the data source is named Data, it can be listed as Sheet1!Data or just Data.
If data source names are qualified with sheet names, then Axiom Software will only scan the listed sheets for the specified data sources. If the data source names are unqualified, then Axiom Software must scan all sheets for the data sources, which can impact performance. It is recommended to use qualified data source names whenever possible.
The list of data lookups must be comprised of either all qualified names or all unqualified names. Mixing qualified and unqualified names is not supported and will result in an error when the data lookups are executed.

The [unnamed] keyword is not applicable here, because all unnamed data sources will be run anyway as part of the Axiom query refresh.

Executing on demand

Data lookups can be executed manually using the Execute Data Lookups command. You can list multiple data sources in a comma-separated list, in the order you want them to be executed. Note the following:

The list of data lookups can be qualified with sheet names, or unqualified. For example, if the data source is named Data, it can be listed as Sheet1!Data or just Data.
If data source names are qualified with sheet names, then Axiom Software will only scan the listed sheets for the specified data sources. If the data source names are unqualified, then Axiom Software must scan all sheets for the data sources, which can impact performance. It is recommended to use qualified data source names whenever possible.
The list of data lookups must be comprised of either all qualified names or all unqualified names. Mixing qualified and unqualified names is not supported and will result in an error when the data lookups are executed.
If you want to run unnamed data sources on open, you can use SheetName![unnamed] to run all unnamed data sources on the specified sheet only, or just [unnamed] to run all unnamed data sources on all sheets. Remember that if you are also running an Axiom query on open, then the unnamed data sources will automatically be executed as part of that refresh.

This command can be used to provide on-demand execution of data lookups. This command is not available to users by default—you must add it to a task pane, ribbon tab, or Axiom form before it is available for use. For more information, see Execute Data Lookups command.

Order of execution

When data lookups are executed, they are executed in the order they are listed in the applicable setting. If multiple data sources have the same name, then the Order parameter in the [DataLookups] tag is used to determine the execution order of those data sources. Any unnamed data sources are treated as having the same name for this purpose. So if you need unnamed data sources to refresh in a particular order, they must use the Order parameter.

If one data lookup row is dependent on the result of another data lookup row, those rows must be in separate data sources and processed in the appropriate order. For more information, see Dependent data lookups.