AX3240
The insert control column or row defines the ranges where data will be inserted (or updated) on the target sheet. Each Axiom query can have one or more data ranges within an insert control column or row.
Specifying the location of the insert control column or row
The location of the insert control column or row is defined on the file's Control Sheet. If the query is a standard vertical query, you specify an insert control column. If the query is a horizontal query, you specify an insert control row.
You can use the Sheet Assistant to define these settings, or you can manually edit the Control Sheet. The Sheet Assistant updates dynamically depending on whether the query is vertical or horizontal:
|
Vertical query |
Horizontal query |
In the Control Sheet, you must expand the appropriate section to reach the setting, either Vertical Configuration or Horizontal Configuration.
Vertical configuration
Horizontal configuration
Keep in mind that once you have specified a row or a column location, it does not update automatically if you insert rows or columns into the sheet, or if you move your Axiom query tags in the sheet. You may want to first design the query on the sheet, figuring out where you want to place items such as headers and subtotals (if needed), and then complete the query settings. If you do change the location in the sheet, you must update the Control Sheet setting.
Placing data range tags in the sheet
The insert control column or row must contain at least one set of data range tags, to specify where the data should be placed in the sheet. To define a data range on the sheet, enter the following tags into consecutive cells within the insert control column or the insert control row (depending on the orientation of the query):
Number (#) represents the number of the Axiom query that this set of tags applies to. For example, if you are placing tags for Axiom query #1, you enter the following:
[aq1]
[stop]
When the Axiom query is processed, data records are inserted after the cell marked with [aq#]. If the query is vertical, the records are inserted below the cell. If the query is horizontal, the records are inserted to the right of the cell.
You can type the tags into the spreadsheet directly, or you can use one of the following tools to automatically insert tags:
-
In the Sheet Assistant, click the arrow button to the right of the Axiom query name. This inserts a basic set of tags at the current cursor location, in the insert control column or row.
- Right-click the cell, then select Axiom Wizards > Insert Axiom Query Data Range > QueryName. Click OK on the resulting dialog to insert a basic set of tags at the current cursor location, in the insert control column or row.
Each Axiom query can have multiple sets of data range tags within a single insert control column or row. In this case you would define a filter to limit the data for each range, otherwise the data in each range would be identical. For more information, see Filtering the data in a data range.
By default, records are populated into data ranges by inserting entire rows or columns. If desired, you can configure the Axiom query's insertion behavior so that it is populated by inserting cells within a designated range instead. If you change the insertion behavior to InsertRange, then you must use additional tags in the data range to specify the insertion range: [Stoprange] to specify the end of the range, and optionally [Startrange] to specify the start of the range. For more information, see Insertion options for Axiom queries and Defining the insertion range when using Insert Range.
NOTES:
-
By default, the specified insert control column or row also holds the data control codes for the Axiom query. When data records are inserted by the query, the key codes for each record are placed in the insert control column or row, to be used for subsequent data updates (if applicable). For more information, see About data control codes for Axiom queries.
-
The action taken on the data range depends on the refresh behavior for the Axiom query. For example, if the refresh behavior is set to rebuild, then any existing data in the range is always deleted, and the range is repopulated with inserted records. If the refresh behavior is set to insert and update, then existing data is updated, and new rows are only inserted if the record doesn't already exist in the range. For more information, see Specifying how data is refreshed in an Axiom query.
-
If you want to dynamically enable or disable an Axiom query, this should always be done by using a formula in the Active setting for the query on the Control Sheet. Showing and hiding data range tags using a formula is not a valid way to dynamically enable or disable a query. Active, non-nested Axiom queries should always have at least one set of data range tags.
- Data range tags must always be present in pairs. An
[aq#]tag must have a[stop]tag, and vice versa.
Insert control column example
The following example is for a standard vertical Axiom query. The first screenshot shows the insert control column before the Axiom query has been run. The designated column is column C, and the [aq1] tag specifies where the data range will begin.
Once the query is run, the data range is populated with data from the database, as shown in the following screenshot:
Note that key codes are automatically placed in the insert control column, because this column is also the data control column. If the Axiom query is set to update instead of rebuild, then each subsequent time the query is run, the existing data rows will be updated, based on the key codes in the data control column.
Insert control row example
The following example is for a horizontal query. The first screenshot shows the insert control row before the Axiom query has been run. The designated row is row 3, and the [aq1] tag specifies where the data range will begin.
Once the query is run, the data range is populated with data from the database, as shown in the following screenshot:
This is a very simple example intended to contrast the insert control structure of a horizontal query from a vertical query.
AX3240
