Axiom query settings

Specifies whether Axiom query data is zeroed when the file is saved. The default setting is Off. This setting is configured on a per sheet basis and applies to all Axiom queries on the sheet.

This feature is intended for data security within reports. If an Axiom query is not set to refresh on open, then the data queried and saved by one user will still be visible when another user opens the file. This may result in users seeing data outside of their data filter, because the query will not update until the user explicitly refreshes the file.

If this option is set to On, then when the file is saved, all Axiom queries in the sheet are zeroed as follows (depending on the refresh behavior for the query):

• If the query is set to rebuild, then all rows in the query are deleted, and the data ranges are collapsed.
• If the query is set to update and/or insert, and zero on update is not enabled, then no action occurs. The rows in the data ranges are left as is.
• If the query is set to update and/or insert, and zero on update is enabled, then the existing rows are left in the data range, but the data values are zeroed. For more information on which columns are zeroed, see the discussion on zero behavior in Specifying how data is refreshed in an Axiom query.

Specifies whether all AQ data range filtersFilters applied to Axiom query tags, to limit the data brought into that data range. Filters are placed within the tags, delimited by a semi-colon -- for example, [AQ1;dept.region='west']. are sent to the server for parsing as part of the query to the database, or just the first data range filter. This helps determine the data to be returned by the query.

• By default this is set to Off, which means that only the first data range filter is sent to the server. In the majority of cases, this is sufficient to ensure that the data returned by the data query will be compatible with all data range filters in the report.

• If On, then all data range filters for the query are sent to the server. This may be necessary in cases where the data range filters specify very different sets of data. Enabling this option will increase the complexity of the data query statement and therefore may slow report performance. It is not recommended to enable this option unless it is necessary for the data query. Contact Axiom Software Support if you need assistance determining whether this option should be enabled.

The name of the Axiom query. This name is required for templates/plan files, and is optional for all other files. If a query has a name, the name is used whenever lists of Axiom queries are displayed.

The name should be unique and descriptive. When processing plan files, you are prompted to choose which Axiom queries to update by name.

Specifies the primary table to query data from when refreshing the file.

In most cases, the primary table is a data table (such as GL2019), but it can be any table. For example, the primary table could be a reference table such as DEPT, if you wanted to create a list of departments or department attributes.

Axiom queries can query data from multiple tables. The primary table setting simply specifies the base table for the query.

If the primary table is not specified, Axiom Software determines the primary table based on the first data column in the field definition. This is primarily intended for situations where you are using column alias names in the field definition, and the table that the alias names point to may change over time.

For more information, see:

• Specifying the primary table for an Axiom query
• Using multiple tables in an Axiom query

Defines the level at which to sum or group the data returned by the query. You can enter multiple column names, separated by a semi-colon.

You can use full Table.Column syntax or "column-only" syntax. If the table name is omitted, the primary table for the Axiom query is assumed, except in cases where the column is a validated column, in which case the lookup table is assumed. For example, an entry of DEPT will be interpreted as DEPT.DEPT. However, if you want to sum by DEPT.Region, the full Table.Column syntax is required.

If this setting is left blank, then by default the data is summed using the lookup columns for the key columns of the primary table.

For more information, see Defining the sum level for an Axiom query.

Specifies the database columns to sort the data by, prior to inserting it into the target sheet. This setting does not apply when only updating data.

You can enter multiple column names, separated by a semi-colon. You can specify ascending (asc) or descending (desc) after each field name. The default is ascending if nothing is specified.

For example: Dept asc; Acct desc

You can use full Table.Column syntax or "column-only" syntax. If the table name is omitted, the primary table for the Axiom query is assumed, except in cases where the column is a validated column, in which case the lookup table is assumed. For example, an entry of DEPT will be interpreted as DEPT.DEPT.

If this setting is left blank, and no spreadsheet sort is specified, then the data is sorted by the columns specified in the Sum data by these columns setting, in ascending order.

For more information, see Defining the sort for an Axiom query.

Specifies the spreadsheet columns to sort the data by when the query is refreshed.

You can enter up to three column letters, separated by a semi-colon. You can specify ascending (asc) or descending (desc) after each column letter. The default is ascending if nothing is specified.

For example: C asc; D desc

For more information, see Defining the sort for an Axiom query.

Defines a data filter to limit the data query. For example: Dept=1000 or ACCT.AcctGroup='Benefits'.

You can use full Table.Column syntax or "column-only" syntax. If the table name is omitted, the primary table for the Axiom query is assumed, except in cases where the column is a validated column, in which case the lookup table is assumed. For example, an entry of DEPT will be interpreted as DEPT.DEPT.

If this setting is left blank, the Axiom query returns all records in the specified table, summed by the specified sum level. Therefore, a filter is almost always used when setting up a query for a template, so that the data in the resulting plan files are limited by the appropriate plan code. For reports, a data filter is optional.

Note that a user's table access settings (as defined in Security) are always applied to an Axiom query, in addition to any data filter.

For more information, see Defining a data filter for an Axiom query.

If desired, you can limit the data in this Axiom query based on the results of another query. For example, you may want to only include items in this query if those same items are also found in a different table. This is an advanced option.

Special syntax is used to identify the parameters of this "pre-query". For more information, see Limit the data in an Axiom query based on another query.

If desired, you can define a filter to limit the data to be returned by the query. This filter is applied after the initial database query, but before data is returned to the client (so it is still a database-level query, unlike data range filters). This is an advanced option.

The primary use of this feature is to enable filtering based on segment when segmenting your query data; however it can be used for other purposes. For more information, see Organizing Axiom query data into ranked segments.

This is an advanced option that only applies when segmenting your query data. You can list the name of the segment column here, in order to group the query results by segments. For more information, see Organizing Axiom query data into ranked segments.

Specifies whether rows with all zero values are inserted into the sheet.

After the data query, but before inserting data into the sheet, Axiom Software checks the data to determine if any records contain all zero values. If this setting is enabled, zero value records are not inserted into the sheet.

Non-key columns that meet both of the following criteria are evaluated to determine whether a row should be hidden:

• The column data type is Integer (all types) or Numeric.
• The column is from the primary table or an additional data table.

If the primary table is a data table, Integer and Numeric columns on lookup reference tables are ignored—meaning these columns may have values, but the row is still suppressed if all applicable data table columns have zero values. There is one exception: reference table columns are considered if the column classification is Values and the numeric type is Currency.

Specifies the maximum number of records that can be returned by an Axiom query without triggering a warning. You can leave this blank to use the system configuration setting, or you can enter a number to set the threshold for this particular query.

This setting is intended to prevent users from accidentally bringing in an extremely large number of records, potentially resulting in excessive processing times or system errors. If the number of records in the query exceeds this setting, the user is warned of this and asked if they want to continue.

NOTES:  

• The only user options are to cancel the query or continue with the full number of records. This setting defines the threshold for the warning, it does not limit the query to the specified number of records.
• The warning only applies when users manually refresh Axiom queries. If the query is processed by Scheduler, the processing will fail if the query exceeds the threshold.

For more information on defining system configuration settings, see System configuration settings.

If desired, you can limit the query results to the top N records for the query—such as top 5, top 10, or top 50. Enter the number of records that you want returned.

When using this setting, the database sort order of the query is very important as that will determine which records are returned. For more information, see Return the top N records for an Axiom query.

Specifies the process definition ID to use when querying plan file process columns. You can use the GetProcessInfo function to return the ID for a process definition, or you can hover over the definition in Axiom Explorer or the Explorer task pane to read the ID on the tooltip.

When the primary table of an Axiom query is the plan code table of a file group, you can optionally include process columns in the query to return process information for each plan code. For more information, see Reporting on plan file processes.

Specifies the insertion behavior for the query, when records are inserted into the data ranges.

• Insert (default): New records are inserted by inserting rows or columns within the data ranges.
• InsertRange: New records are inserted by inserting cells within the data ranges.

If InsertRange is selected, then you must specify the insertion range using [Stoprange] and [Startrange] tags. The [Startrange] tag is optional; if omitted, the insertion range starts at the [AQ#] tag. The insertion range tags must be placed in the same column or row as the [AQ#] tag (depending on the query orientation).

This option does not apply if the refresh behavior for the query is set to update-only.

For more information, see:

• Insertion options for Axiom queries
• Defining the insertion range when using Insert Range

Specifies whether spreadsheet grouping is applied to the Axiom query results. If Off, grouping is not applied and the results are presented as normal.

If On, grouping is applied and the query results are grouped by data ranges. The row containing the [AQ#] tag is used as the "anchor" row for the grouping. All rows in the range, including the row with the [Stop] tag, are contained in the group.

This option is most typically used in cases where you have multiple data ranges, or multiple nested Axiom queries, and you want to group sections in the results so that they can be expanded and collapsed.

For more information, see Using spreadsheet grouping with an Axiom query.

Specifies whether the query populates data vertically or horizontally. If Off (the default), the query is a standard vertical query. If On, the query becomes a horizontal query.

Certain settings, such as control rows and columns, differ based on the orientation of the query. You will complete either the Vertical Configuration section or the Horizontal Configuration section, depending on the orientation of the query. All other settings apply to both query orientations, unless otherwise noted.

For more information, see:

• About Axiom queries
• Using horizontal Axiom queries

Specifies the row or rows in the target sheet where database column names are placed. Data from the specified database column will be brought into the sheet column (within the Axiom query's data range) when the file is refreshed.

To specify the row, you can enter a single row number (1), or you can enter the row or rows as a range (1:1 or 10:12). When entering a row range, make sure that Excel is evaluating the range as text. If the cell is not formatted as text, you can force a text entry by entering an apostrophe in front of the range ('10:12).

For more information, see Creating the field definition for an Axiom query.

Specifies the column where data range tags are placed. These tags define the beginning and end of each data range.

An Axiom query can have multiple data ranges, to determine where data rows are inserted into the sheet.

NOTE: By default, this column also serves as the data control column for the Axiom query. When the query is refreshed and the data range is populated, the cells in between the data range tags will contain the key codes for each row.

For more information, see Creating the insert control column or row for an Axiom query.

Specifies the database table and column where calc method assignments have been defined. For example, ACCT.AssignCM. This setting only applies to templates/plan files.

Special syntax is available if you need two levels of lookup in order to assign calc methods (for example, if the calc method used for an account varies based on the department type).

For more information, see:

• Using a single assignment column
• Using multiple assignment columns by plan code type

Use this section if you want to read the calc method assignments from a sheet in the file, rather than pointing directly to a database column. Within the sheet, you can use any methodology to create the list of assignments, including using another Axiom query to build the list dynamically per plan code.

Complete all four settings as follows:

• Key column name: The name of the key column for the calc method assignments. For example, ACCT.
• Sheet Name: The name of the sheet that contains the calc method assignments. For example, CMAssign.
• Key column in sheet: The column in the sheet that contains the key codes. For example, enter C if the account codes are in column C.
• CM Assignment column in sheet: The column in the sheet that contains the calc method assignments. For example, enter D if the assignments are in column D.

For more information, see Reading calc method assignments from a sheet.

Specifies the column or columns in the target sheet where database column names are placed. Data from the specified database column is brought into the sheet row (within the Axiom query's data range) when the file is refreshed.

To specify the column, you can enter a single column letter (D), or you can enter the column or columns as a range (D:D or D:F). When entering a column range, make sure that Excel is evaluating the range as text. If the cell is not formatted as text, you can force a text entry by entering an apostrophe in front of the range ('D:F).

For more information, see Creating the field definition for an Axiom query.

If enabled, the query will be refreshed when a user manually refreshes the sheet. For example, the user could click the Refresh button in the ribbon, or click a task pane item that is configured to perform a refresh, or press F9.

This option is enabled by default. If you disable this option, then the query will not refresh when a user manually refreshes the sheet. For example, you might disable this option if you have a query that you only want to run when the file is opened, and not during any subsequent manual refreshes.

NOTE: In plan files only, users cannot manually refresh Axiom queries unless they have the Run AQs in Plan Files permission. If you have a query that needs to be run in plan files, but you do not want to give users this permission, then you can enable Refresh on file open for the query.

If enabled, the query will be refreshed when the file is processed by a feature that includes performing a refresh, such as Process Plan Files, File Processing, or Process Document List. Some of these features, such as Process Plan Files, allow you to disable refreshing particular queries as part of that particular processing job. For other features, the query will always be refreshed if this option is enabled.

This option is enabled by default. If you disable this option, then the query will not be available to processing features that include performing a refresh.

If enabled, the query will be refreshed when the file is processed by the Scheduler task Process Template List. This option only applies to file group template files; it has no effect in any other file type.

When the query is processed by Process Template List, the Last refresh time of the query is updated in the template. This feature is intended to enable use of time-stamped Axiom queries in virtual plan files, by time-stamping the query in the template.

If enabled, the query will be refreshed automatically whenever the file is opened, whether by a user or by a system process (with one exception: Process Plan Files—see notes below). This setting is disabled by default.

When this setting is enabled, administrators can use the Open Without Refresh option if you need to open the file without running the Axiom query. For more information on this option, see Opening an Axiom file without refreshing data.

NOTES:  

• If the file uses refresh forms (refresh variables or Axiom form as refresh form), the refresh form will only display on file open if you explicitly configure it to do so using the Refresh Forms Run Behavior setting on the Control Sheet.

• In plan files, queries configured to refresh on open are always run when the file is opened by a user, regardless of whether the user has the Run AQs in Plan Files security permission. This can be used if you have a query that needs to be run to enable functionality in the plan file (such as for a drop-down list), but you do not otherwise want users to be able to refresh Axiom queries.

• This setting is ignored by Process Plan Files. However, the query can still be selected for processing if it is also enabled to Refresh during document processing.

This option only applies when the file is processed using the multipass option of File Processing. If enabled, the query will be run once before multipass processing begins. If you do not want the query to also be run during each individual pass, then you should disable Refresh during document processing.

This option is disabled by default. You might enable this option if you have a query that needs to be run to bring an initial set of data into the file, but you don't want to set the query to refresh on open because the query is quite large and causes an unnecessary delay when opening the file for editing.

If enabled, the query will be refreshed automatically after a save-to-database occurs, whether the save is initiated by a user or by a system process. The refresh occurs regardless of whether any data changes were actually saved. The Axiom query is refreshed after all save-to-database processes in the workbook are completed.

This setting is disabled by default. You might enable this option if you have a configuration where the save-to-database process adds or updates a record that would be returned by an Axiom query in the workbook. In order for the data in the Axiom query to be updated automatically for the changed data, you would need to enable this option (otherwise the user would not see the changed data until they manually refresh). This feature may be especially useful in Axiom form design.

NOTE: The behavior of this option depends on whether the sheet-level setting Convert Axiom Query results to zero on save is also enabled. If "zero on save" is enabled, then the Axiom query is first zeroed, then the file is saved, then the Axiom query is refreshed with data. This order is so that the data is not saved within the file. However, if "zero on save" is not enabled, then the Axiom query is refreshed before the file is saved, which means that the current data will be saved within the file. This does not apply when interacting with Axiom forms, because in that case the file is never saved.

Specifies one or more data lookup queries to run after the Axiom query is refreshed, by listing the names of the DataLookup data sources. Separate multiple data source names with commas. Multiple data sources will be run in the order specified.

The list of data source names can be qualified with sheet names (such as Sheet1!Data) or unqualified (just Data). If qualified, then Axiom Software will only scan the listed sheets for the data sources. If unqualified, then Axiom Software must scan all sheets for the data sources, which can impact performance. All items in the list must be either qualified or unqualified. Mixing qualified and unqualified names will result in an error when the data sources are executed.

All unnamed DataLookup data sources will also be run when any Axiom query is refreshed. For more information, see Executing data lookups.

If enabled, the Axiom query will only refresh if the primary table has been modified since the query was last executed. If the primary table has not been modified, then the query will not be run and the data in the sheet will be left as is. This is a performance optimization feature that is intended for queries that meet specific criteria. For more information, see Refresh data only when table has been modified (time-stamped Axiom queries).

The Last refresh time is used to determine when the query was last executed.

The date and time the query was last executed. This value is automatically written to this field when the query is run. Currently, this value is only used if Refresh only if primary table changed since last refresh is enabled. Otherwise, it is for information only.

The last refresh time is shown in UTC (Universal Coordinated Time); it is not converted to the local time of the client where the refresh occurred.

This field is system-controlled and should not be manually modified. The only exception is that occasionally you may need to clear this field in order to "reset" the last refresh time.

NOTE: By default, this field is not populated when executing an Axiom query in a file group template file. This is so that when plan files are created from the template, the field starts out blank and can be populated for the plan file. However, if the Axiom query is refreshed by Scheduler using the Process Document List task, then the last refresh time is populated.

Determines whether Axiom queries are rebuilt on refresh. If On, all content in the data range is deleted when the file is refreshed, and the data ranges are rebuilt. In this case, the insert, update, and zero settings are ignored.

If Off, the existing content in data ranges is retained, and data is updated according to the other refresh behavior settings.

Determines whether data values previously inserted by the Axiom query are zeroed before subsequent updates. This setting only applies if Rebuild Data Ranges is Off.

Determines whether data values previously inserted by the Axiom query are updated on subsequent refresh. Formatting and formulas are not updated. This setting only applies if Rebuild Data Ranges is Off.

Specifies a conversion target so that the Axiom query returns converted data. This is the only required entry for data conversions.

You can enter a fixed value that will apply to all records, or you can look up the value on a record by record basis from a grouping column in a lookup reference table. The fully qualified name must be used—for example, DEPT.TargetCur.

NOTE: If you use a grouping column, it is recommended to also include the grouping column in the field definition for the query, to identify the conversion that was applied for each individual record.

In either case, the conversion target must be a value that is found in the "to" column for the associated conversion table. The conversion table is determined on a column by column basis (for each data column entry in the field definition), using the conversion configuration for the associated table.

If a table in the Axiom query is not configured for data conversions, then the conversion settings are ignored and the "as is" data is returned for that table (unless all tables in the query are not configured for data conversions, in which case an error results).

If desired, specify a conversion scenario to override the default scenario for the data conversion.

The entry must be a value that is found in the "scenario" column for the conversion table. The conversion table is determined based on the primary table for the query.