AX2063

Defining a data filter for an Axiom query

The Data Filter setting for an Axiom query limits the data to be queried from the database. Technically speaking, the data filter is the WHERE clause of the data query.

For example, you might only want to query data for a particular department, region, or manager. Or you might want to exclude specific departments, accounts, or account categories from the query.

The data filter uses standard filter criteria syntax.

NOTE: A user's table filters (as defined in Security) are always applied to an Axiom query, in addition to any data filter. Therefore, the data returned by an Axiom query may vary, depending on the user's security settings.

What can be used in the data filter

Valid data filter entries depend on the primary table and on other tables included in the query.

When the primary table is a data table, and no other data tables are included in the query, the following database columns can be used to define the data filter:

• Any column in the primary table. You can use fully qualified Table.Column syntax or just the column name. If just the column name is used, and the column is a validated columnA column that has a defined lookup relationship with a column in another table, to define the valid values for the column. For example, if Acct.Acct is assigned as a Lookup Column for GL2014.Acct, then GL2014.Acct is known as a validated column and Acct.Acct is known as a lookup column., the associated lookup table will be assumed (such as ACCT.ACCT for an entry of ACCT).

• Any column in a lookup tableA table that is the target of a Lookup Column relationship in another table. For example, if the column GL2018.Acct links to the column Acct.Acct, then the Acct reference table is known as a "lookup reference table".. You must use fully qualified Table.Column syntax. Multiple levels of lookups can be used.

When the primary table is a data table and additional data tables are included in the query, the following database columns can be used to define the data filter:

• Any shared validated columnA column that has a defined lookup relationship with a column in another table, to define the valid values for the column. For example, if Acct.Acct is assigned as a Lookup Column for GL2014.Acct, then GL2014.Acct is known as a validated column and Acct.Acct is known as a lookup column. in the data tables, key or non-key. The column reference must be to the shared lookup column, not to the column in the data tables. For example, you can specify DEPT (which assumes the DEPT table), or DEPT.Dept, but not GL1.Dept.

• Any column in a shared lookup tableA table that is the target of a Lookup Column relationship in another table. For example, if the column GL2018.Acct links to the column Acct.Acct, then the Acct reference table is known as a "lookup reference table".. You must use fully qualified Table.Column syntax (such as DEPT.Region or ACCT.Category). Multiple levels of lookups can be used.

• Any shared non-validated column in the data tables, key or non-key. In this case you must specify only the column name—do not use fully qualified syntax (such as Detail where both tables contain a non-validated column named Detail). This results in an intentional ambiguity which allows the specified column to apply to all data tables in the query. However, note that if any lookup tables also contain a column with the same name, the ambiguity will not be allowed and the column will be invalid for use in this context.

If the primary table for the query is a reference table rather than a data table, then only columns in the primary table and any lookup reference tables can be used. Data tables cannot be used in the data filter, even if the data table is eligible to be included in the field definition.

Most filters use a reference table to filter the data. For example:

Data filters for templates

When you create plan files from templates, you want to query the data for each individual plan code. Therefore the data filter for the Axiom query must contain a statement that limits the data to the current plan code.

The best way to do this is to reference the plan code somewhere in the file by using =GetFileGroupProperty("PlanFile"). Then, reference that cell in your data filter. For example:

If the plan code table is DEPT and the GetFileGroupProperty function is located in cell C3 of the Budget sheet in a template, this example filter dynamically limits data according to the current plan code. For example, if the current plan file is for department 100, this filter would resolve to Dept=100.

Remember, you can have multiple sheets and multiple Axiom queries in a template. Only those queries that relate to the plan data for the current plan code must have a data filter to restrict the data by plan code. You can have additional queries in the template that query data from any table, using any filter (or no filter).

Other filtering options for Axiom queries

The Axiom query data filter affects the data returned for the entire query. If you want to filter only specific areas of the query, you can do one or both of the following:

• Column filters: You can place a filter on a database column code in the field definition, to filter the data queried from that particular column. For more information, see Filtering the data coming into a spreadsheet column (column filters).
• Data range filters: You can place a filter on a data range in the insert control column or row, to filter the data displayed in that data range. For more information, see Filtering the data in a data range.

Sheet filters are always honored when running Axiom queries. If you want to filter all of the data queries on a sheet, including GetData functions, you can define a sheet filter. For more information, see Defining sheet filters.

AX2063