AX2080
Specifying how data is refreshed in an Axiom query
Configuring the refresh type for an Axiom query
You can use the Sheet Assistant to specify the refresh type for the query, or you can modify the Control Sheet directly.
On the Sheet Assistant, you specify the desired refresh type as one of the following: Rebuild, Insert Only, Update Only, or Insert and Update. If an update option is selected, then you can specify whether zero is enabled by selecting or clearing the Zero on Update check box (by default, it is selected when an update option is selected).
On the Control Sheet, each option is individually controlled as an On or Off selection within the Refresh behavior section. If rebuild is set to On, then the settings for insert, update, and zero are ignored.
NOTE: On the Control Sheet, it is possible to set all refresh options to Off. In this case, no action occurs when the query is refreshed. If you do not want a particular query to refresh with data, you should inactivate the query rather than disable all refresh options.
Rebuild behavior
If rebuild is enabled for an Axiom query, then all existing content in data ranges is deleted, and data ranges are rebuilt. This is the default setting for all Axiom queries.
When rows or columns are inserted as part of a rebuild, the Insertion Behavior setting for the query determines whether the data ranges are rebuilt by deleting and then reinserting entire rows or columns, or whether only the cells in the specified range are deleted and inserted. For more information, see Insertion options for Axiom queries.
This setting is only used in circumstances where you do not need to retain any inputs or changes to the rows within data ranges (or columns, for horizontal queries). Data ranges are completely rebuilt, using the most current data and the most current versions of calc methods. Generally, rebuild is used for report files and not templates/plan files.
However, if appropriate, rebuild can be used in any context. For example, imagine that you wanted to generate a list of accounts in a plan file, to create a drop-down list for users to select an account number for a newly inserted calc method. In this case you would use rebuild to ensure that the list of accounts matches the current list in the database.
Either the database column sort or the spreadsheet column sort can be used to sort the data in the rebuilt range.
Insert behavior
If insert is enabled for an Axiom query, then any records resulting from the data query that do not currently exist in the data range are inserted into the data range. Queries can be set to insert only, or used in conjunction with update.
When rows or columns are inserted, the Insertion Behavior setting for the query determines whether entire rows or columns are inserted, or whether only the cells in the specified range are inserted. For more information, see Insertion options for Axiom queries.
The database sort settings only apply if no data currently exists in the range. If data already exists in the range, then any new rows of data are inserted at the bottom of the range. If spreadsheet sort settings are defined, the data range is sorted after the refresh.
NOTE: If a query with multiple data ranges is set to insert, and a record exists in one data range but does not exist in another data range, then the record will not be inserted in the other data ranges. However, if the record does not exist in any data ranges, then it will be inserted in each eligible data range.
Update behavior
If update is enabled for an Axiom query, then data values in the data ranges that correspond to field definition entries are replaced with new data from the database. Queries can be set to update only, or used in conjunction with insert.
Only matched data is updated. If a data point exists in the data range but does not exist in the query (unmatched data), then the update process ignores that existing data point. If you do not want to retain this unmatched data as is, then you can use the zero option in conjunction with the update.
NOTE: It is strongly recommended to enable zero when using update, to ensure that no stale data displays in the sheet. You should only disable zero if you explicitly need to keep the old unmatched data.
The update process only updates data; it does not apply calc methods. If changes have been made to calc methods (either in the library or to the in-sheet calc method), those changes are not applied to existing data in the data ranges. The only way to apply calc method changes to existing data is to perform a rebuild. Note that if insert is enabled in conjunction with update, then any new records inserted will use the most-current versions of calc methods.
The database column sort does not apply to this option; the spreadsheet column sort does apply.
NOTE: The update option uses key codes in the data control column (or row) to match and update each record of data. If data records were originally inserted by using rebuild or insert, then Axiom Software automatically placed the appropriate key codes for each record. If data records were not inserted by the Axiom query, then you must populate the data control column (or row) with the appropriate key codes in order to facilitate the update. For more information, see Manually placing data control codes for update queries.
Zero on update behavior
If zero is enabled for an Axiom query, then existing data values in the data range that correspond to field definition entries are zeroed before they are updated. This option only applies if update is enabled.
This setting is intended for circumstances where a data point existed in the database when the data range was first populated, but that data point was subsequently deleted from the database. (Or, the data filter for the Axiom query was changed to exclude records that were previously included in the query.) If zero is not enabled, then the original value is retained in the data range because the update option ignores it. When the zero option is enabled, the deleted data value is replaced with a zero.
The following columns are not zeroed:
- Columns used as the Axiom query "sum by" level
- Key columns and alternate key columns
- Validated columns
- Columns from reference tables, when the primary table of the query is a data table
However, if a column that would normally not be zeroed uses AxAggregate to apply an alternate aggregation (such as RowCount), then the column is zeroed.
