AX2064

Filtering the data in a data range

You can place a filter on an Axiom query tag, to limit the data to be brought into a particular data range. The filter statement must be appended to the end of the Axiom query start tag, separated by a semi-colon. For example:

When the file is refreshed, this range will be limited to data for accounts belonging to the benefits category.

Data range filters can be used to group data into multiple sections, allowing for subtotals and other formatting. For example, you could have multiple data ranges with filters as follows:

When the file is refreshed, there will be two data ranges, one containing the benefit accounts, and one containing the compensation accounts. Both ranges use the settings for Axiom query #1, the data range filter simply limits the data displayed in each range.

When to use data range filters

Generally speaking, data range filters should only be used as a means to organize the data in the sheet into multiple sections. It should not be used to filter the data coming back from the database.

The data range filter is applied after the data has already been returned from the database, to determine where that data is placed in the sheet. Theoretically, this means that you could query 5,000 rows from the database but only place 100 of those rows in the sheet due to the data range filters. This is known as unmatched data, and it should be avoided for performance reasons. If you really only need 100 rows, then you should apply a filter at the database level instead (such as by using the Axiom query Data Filter), so that the database query returns only the 100 rows that you need. The data range filters can then be used to determine where those 100 rows are placed in the sheet.

For more information on unmatched data, see Checking for unmatched data in an Axiom query.

Valid data range filters

The data range filter can be based on any column that would be valid in the Axiom query Sum By or in the field definition—in fact, in most cases the filter column is also being used in one or both of these places.

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.

NOTE: When querying multiple data tables, it is not possible to use a shared, non-validated column in the data range filter and have it apply to all of the data tables. This type of ambiguity is allowed in the main Axiom query Data Filter, but it is not supported in data range filters. The non-validated column must be fully qualified in this context.

It is recommended to include the filter column in the sum by, to ensure that data is summed at the same level that you want to present it in the sheet. If the filter column is not part of the sum by, then data may not be returned in the data ranges as you expect if the records being summed do not have the same value for the filter column.

If the filter column is not included in the sum by or the field definition, then Axiom Financial Institutions Suite will automatically include the filter column from the first data range in the database query, as if it were part of the field definition. This is to ensure that the query includes the data necessary to apply the data range filter in the sheet. However, if you have multiple data range filters that use different columns, and all of those columns are not represented in the sum by or in the field definition, then you must enable the following setting in the Data Options section of the Control Sheet: Enable full AQ query validation mode. This will cause all columns used in data range filters to be included in the database query.

Creating a filtered data range

You can manually type the filter into the data range tag, or you can use the data range wizard to automatically create a filtered tag with the correct syntax:

1. Place your cursor in the desired location, and then right-click and select Axiom Wizards > Insert Axiom Query Data Range > AxiomQueryName.
2. In the Insert Data Range dialog, type a filter or use the Filter Wizard to build a filter. When you click OK, the data range tag will be inserted into the appropriate control column or row, with the filter appended to the tag.

If a tag already exists at a particular location, you can double-click the [aq#] tag to add or edit a filter on the existing tag.

Example data range filters

In the following example, there are two data ranges, each with a separate filter:

When the Axiom query is run, each data range is populated only with the data that meets the filter. In this case, the first range displays data for department 40000 and the second range displays data for department 45000.

Although these particular data ranges were "hard-coded," a good way to automate multiple data range filters is to use "nested" Axiom queries. For example, if you wanted this report to contain one section per department, it would be time-consuming and inflexible to manually create each data range filter for each department. Instead, you could have one "parent" Axiom query that brings in departments and dynamically builds out the data ranges for a second "child" Axiom query. The child query would be the query that brings in the account data.

AX2064