AX2073

Insertion options for Axiom queries

The insertion behavior setting for an Axiom query determines how data records are inserted into a data range (and also how they are deleted, if the refresh behavior is rebuild).

Axiom queries have two options for insertion behavior: Insert Full Row/Column and Insert Range. Insert Full Row/Column is the default setting that will be used for most queries. Insert Range is an advanced option that is primarily intended for horizontal queries, but may also be appropriate for certain vertical query configurations.

Insertion behavior settings apply if the refresh behavior for the Axiom query is set to either rebuild or insert. If a query is set to update only, then insertion behavior does not apply.

NOTE: If you are configuring a horizontal Axiom query within a template, plan file, or file group utility, then you must use Insert Range if the sheet uses calc methods. Inserting full columns is not supported in this context.

Configuring the insertion behavior

You can use the Sheet Assistant to specify the insertion behavior for the query, or you can modify the Control Sheet directly.

In the Sheet Assistant, the setting is located in the Refresh Settings section, as Insert Type. The options are Insert Full Row/Column and Insert Range.

On the Control Sheet, the setting is located in the Display/User Interaction Options section, as Insertion Behavior. The options are Insert and InsertRange.

How Insert Full Row/Column works

For standard vertical Axiom queries, if Insert Full Row/Column is specified as the insertion behavior, then the Axiom query is populated by inserting new rows within the data ranges (the area flagged by the [AQ#] and [Stop] tags). Any content below the [Stop] tag is pushed down.

If the refresh behavior for the query is rebuild, then all rows within the data range are first deleted, and then new data is inserted. If the refresh behavior is insert and update, then existing rows are retained, but any new data rows are inserted below the existing data.

If the query is a horizontal query, then the same behavior applies, except that columns are inserted and deleted instead of rows. This behavior is often not desirable for a horizontal query, especially if the horizontal query is being used to build out components for a vertical query. If entire columns are inserted or deleted, this may adversely affect settings, formulas, and formats needed for the vertical query. In many cases, horizontal queries will use the Insert Range behavior instead.

NOTE: When the insertion behavior is Insert Full Row/Column, the row height or column width of the calc method (depending on the query orientation) is applied to new records coming into the data range.

How Insert Range works

If Insert Range is specified as the insertion behavior, then the Axiom query is populated by inserting new cells within its designated range. This is equivalent to inserting within a spreadsheet and choosing to shift cells down or right, rather than inserting entire rows and columns.

Insert Range is most often used for horizontal queries, when they are being used to build out components for a vertical query. This way you can refresh the horizontal query without affecting any content in the sheet that lies above or below the horizontal data range.

Most vertical queries do not need to use Insert Range, because all necessary content for the data rows is contained within the field definition and calc method, and any additional settings for the query are normally maintained at the top of the sheet, in an area unaffected by row insertion and deletion. However, it may be appropriate for certain vertical query configurations. For example, you may want to create a "portal-style" view on your home page, displaying information side by side. Using Insert Range, you could have a query on the home page that could be updated without impacting the information displayed to either side of the query.

When using Insert Range, you must designate the range to be affected by the cell insertion. This is accomplished by placing [Startrange] and [Stoprange] tags in the same row or column as the [AQ#] tag (depending on the query orientation). For more information, see Defining the insertion range when using Insert Range.

AX2073