Axiom query settings

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.

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.

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

(missing or bad snippet)

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.

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

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.

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.

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.

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.

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 Financial Institutions Suite will only scan the listed sheets for the data sources. If unqualified, then Axiom Financial Institutions Suite 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 dependent tables have been modified since the query was last executed (also known as time-stamped refresh behavior). If none of the dependent tables have 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 dependent tables have been modified (time-stamped Axiom queries).

The dependent tables for the query are the primary table and any additional tables listed in the Additional table dependencies field. The Last refresh time is used to determine when the query was last executed.

Specifies one or more additional tables to consider if Refresh only if primary table changed since last refresh is enabled. Separate multiple table names with commas. If left blank, then the primary table is the only dependent table for the time-stamped refresh behavior.

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 Template 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. (missing or bad snippet)

Determines whether data values previously inserted by the Axiom query are updated on subsequent refresh. Formatting and formulas are not updated. (missing or bad snippet)

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.