AX1705
Axiom queries can be used to bring data, formatting, and calculations into Axiom files, or to update existing data rows. Axiom queries are set up using the following components:
- Axiom query settings defined on the file's Control Sheet.
- Various control rows and columns defined in the target sheet. These control rows and columns specify the data to be brought into the sheet and where, and in some cases also define the formatting and formulas to be applied (in-sheet calc methods).
- Calc methods defined in a calc method library for the target sheet. This only applies when setting up Axiom queries for templates/plan files.
Any sheet can have an Axiom query, and each sheet can have multiple Axiom queries.
Vertical and horizontal queries
An Axiom query can be oriented vertically or horizontally.
-
Vertical queries: Vertical queries bring records into the sheet as rows. This is the "standard" type of Axiom query that is used by default on the Control Sheet. All queries are vertical queries unless you explicitly flag the query as a horizontal query.
- Horizontal queries: Horizontal queries bring records into the sheet as columns. Horizontal queries can be used on their own for certain specialized data configurations, or as a "building block" for a second, vertical Axiom query. For example, you can use a horizontal query to dynamically build out the field definitions for a vertical Axiom query. Horizontal queries generally require more advanced report knowledge to set up.
Most Axiom query settings apply to either type of query. However, some settings can only be used with standard vertical queries, such as spreadsheet grouping and calc method libraries.
The orientation of the query determines how the control rows and columns must be placed in a sheet. For example, the field definition for a query contains the database column names for the data to be brought into the sheet. If the query is vertical, then the field definitions are placed in a designated row, so that the associated data can be brought in as rows. If the query is horizontal, then the field definitions are placed in a designated column, so that the associated data can be brought in as columns.
Axiom query settings on the Control Sheet
The settings on the Control Sheet define the following for an Axiom query:
- Data query settings, such as the primary table to query, what level to sum rows by, and whether to apply a data filter
- The location of various control rows and control columns in the target sheet
- Data refresh and insertion options
- Data sorting options
- Calc method options
For more information, see Axiom query settings.
Axiom query settings can be defined using either the Sheet Assistant or the Control Sheet. Some advanced query settings are only available on the Control Sheet.
NOTE: When an Axiom query is processed, the user's table filters (as defined in Security) are always applied to the query, in addition to any data filter defined for the query. This means that the data returned by an Axiom query may vary, depending on the user processing the query.
Control rows and columns for Axiom queries
The following control rows and columns must be defined within the target sheet. Whether each item is a row or a column depends on whether the query is a vertical query or a horizontal query.
| Control Row or Column | Description | More Information |
|---|---|---|
|
Field definition |
This item contains the database column names for the data to be brought into the target sheet. The column names are placed in the columns or rows where you want the data to be populated. Vertical queries use field definition rows, and horizontal queries use field definition columns. The field definition for a query can use multiple rows or columns. |
|
|
Insert control |
This item contains the keywords to specify the beginning and end of a data range on the target sheet. You can have multiple data ranges for each Axiom query, using different filters to create sub-groups of data. Vertical queries use an insert control column, and horizontal queries use an insert control row. |
Creating the insert control column or row for an Axiom query |
|
In-sheet calc method |
This item contains the formatting and formulas that you want to apply to records of data as they are inserted. Vertical queries use in-sheet control rows, and horizontal queries use in-sheet control columns. The in-sheet calc method for a query can use multiple rows or columns. For plan files, in-sheet calc methods are optional. Plan files can use the calc method library instead. |
|
|
Data control |
This item is used by Axiom Software to record the key codes for each record of data inserted into the worksheet. These key codes can be used to perform updates of existing data. Vertical queries use a data control column, and horizontal queries use a data control row. By default, the data control column/row is the same as the insert control column/row. |
Example Axiom query control rows and control columns (vertical query)
If an Axiom query is active in an Axiom spreadsheet file, the query can be run to bring data into the file. The methods used to run a query depend on the type of file and on user permissions. The following features represent the primary means of running Axiom queries.
| Feature | Description |
|---|---|
|
Refresh |
The Refresh feature in the File Options group can be used in any Axiom file to run all active Axiom queries in the file, or for the current sheet only. This feature is most typically used in reports, to refresh the file with the most current data. In plan files, Refresh is only available to administrators and users with the Run AQs in Plan Files security permission. This is to prevent users from accidentally overwriting data in plan files. In most cases, Axiom queries are only run in plan files by administrators using the Process Plan Files utility. If an Axiom query needs to be run in plan files, it can be configured to automatically refresh when the file is opened. Administrators also have access to the Axiom Designer tab, which provides additional refresh options such as the ability to refresh a single Axiom query at a time. See Using the Axiom Designer tab. |
|
Process Plan Files |
The Process Plan Files utility can be used to selectively process Axiom queries in plan files. Only active queries can be selected. This utility is typically used to bring data into plan files when they are initially created. If your plan files are designed to accommodate periodic data updates, this utility can be used to update plan files in batch. From the file groups menu, this utility is only available to administrators and users with the Process Plan Files security permission. The utility can also be run using Scheduler. |
|
File Processing |
The File Processing feature refreshes the file, including running Axiom queries, and performs a file processing action such as taking a snapshot of the file or creating an export file. If a file is set up to use file processing, then any user with access to the file can execute the file processing. File processing can also be run using Scheduler. |
|
Process Document List |
This Scheduler task can be used to process all active Axiom queries in any Axiom file. Its primary intent is to process driver files or report utilities. |
Axiom queries can also be configured to automatically run when a file is opened, or after a save-to-database has occurred, or to support various specialty features.
