AX2063
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:
(missing or bad snippet)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:
(missing or bad snippet)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:
DEPT=2000 limits the data to department 2000.
DEPT.Region='North' limits the data to departments belonging to the North region.
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:
="DEPT="&Budget!C3
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
