AX2067

Filtering the data coming into a spreadsheet column (column filters)

You can filter the data coming into a particular spreadsheet column by appending a filter criteria statement to the database column code in the Axiom query field definition. This is known as using a column filter or a field definition filter. For example:

When the file is refreshed, the data coming into this column of the spreadsheet (or row, if it is a horizontal query) would be limited to departments assigned to the North region. You might have several columns like this, each one referencing the same database column but restricting the data to a specific region, or manager, etc., for comparison purposes.

Column filters can be based on a lookup reference table, or on the table that the field definition entry belongs to. You can use fully qualified Table.Column syntax, or "column-only" syntax. If the table name is omitted then the primary table is assumed, unless the column is a validated key column, in which case the lookup table is assumed.

Column filters can use compound filters and parentheses in the filter criteria statement.

NOTE: When the primary table is a reference table, column filters can be used on data tables included in the field definition. In the filter, you can use any lookup reference table for the data table, even though that reference table has no relationship to the primary table. For example, if the primary table is Dept, you can include a column filter such as GL2021.M1;Acct.Category='Revenue' to filter the data table column, even though Dept has no lookup relationship with the Acct table.

Naming a filtered column

You can optionally name a filtered column, so that it can be used in the following Axiom query features:

• Data Sort
• Post-Query Filter

Defining a name allows you to use the specific filtered results of the column in these settings, instead of the unfiltered results. For example, you might repeat the same column several times in the field definition, but each time apply a different filter. If you use just the column name in an Axiom query setting, then the setting uses the unfiltered results of the column. But if you define a unique name for a filtered column and then use the unique name in the setting, the setting then uses the filtered results for that specific named column.

To name a filtered column, append a unique name to the base column name using a colon. For example: