AX2282

Defining sheet filters

On the Control Sheet, you can define a sheet-wide filter that filters the data to be returned by data queries on that sheet. The sheet filter applies to Axiom queries and GetData queries (function or data lookup).

If a sheet contains multiple queries and you want to use the same filter on all queries, then using the sheet filter may be easier and more efficient than duplicating the filter on each query.

Sheet filters can be defined by table or by table type. You may want to define a filter by table type if the primary table that is queried in the report is likely to change over time (but will belong to the same table type), or if the report queries data from multiple tables that belong to the same table type.

On the Control Sheet, the sheet filters are located in the Sheet Filters section:

In this example, one sheet filter type is defined, which filters the tables in the GL table type. The filter criteria statement Dept.Region='North' is applied to the Report sheet. Other sheets in this workbook could be filtered or not as desired.

NOTES:  

  • You can configure specific GetData queries to ignore the sheet filter if desired, using an optional parameter. For more information, see the documentation on the GetData function or the GetData data lookup as appropriate.

  • If multiple filters are defined for the same sheet, those filters are concatenated using AND. Therefore, if you are filtering based on the same column, you should create a single compound filter rather than two separate filters. For example, if one filter is Dept.Region='North' and a second filter is Dept.Region='South', those filters will be concatenated with AND, which will result in no data (because no department belongs to both the North region and the South region). Alternatively, a single compound filter such as DEPT.Region='North' OR DEPT.Region='South' (or Dept.Region IN ('North', 'South')) will return data.

  • The Quick Filter feature and the function GetDocumentHyperlink can be used to apply a temporary sheet filter to a file. In this case, the temporary sheet filter does not display on the Control Sheet.

  • Axiom system tables (Axiom.TableName) cannot be used in sheet filters.

To define a filter for a sheet:

  1. In the Sheet Filter Type cell, use the drop-down list to select either By Table or By Table Type.

  2. In the Table or TableType cell, type the name of the table or the table type (depending on the sheet filter type).

    If you do not know the exact name of the table or the table type, you can use the Sheet Assistant to look it up.

    NOTE: The entry here must represent the table or table type that you want to be filtered. For example, if you are querying tables from the GL table type and you want those tables to be filtered by a specific department, then the entry here should be the GL table type (not the Dept table).

  3. In the Filter cell, in the definition column of the sheet to be filtered, enter the filter criteria statement.

    You can type the statement directly, or enter a cell reference to another cell that contains the statement, or right-click in the cell and select Axiom Wizards > Filter Wizard.

By default, the Control Sheet contains five filter rows. If the Filter box is blank for a sheet, then no filter will be applied to that sheet. You can define multiple filters to be applied to the same sheet, or to different sheets.

IMPORTANT: Do not attempt to add more filter rows to the Control Sheet. Remember that you can create compound filter statements on the same row, as long as the sheet filter type and target table/table type are the same. If you cannot combine filter statements and you feel that you need more filter rows, please contact Axiom Support for assistance.

In most cases, the filter should be based on a lookup table. You can use full Table.Column syntax or column-only syntax, depending on the column to be filtered by. For example:

  • DEPT.Region='North' limits the data to departments belonging to the North region.
  • DEPT>200 limits the data to departments greater than 200. Because the filter column is a validated key column, column-only syntax can be used to assume the lookup table (the filter is interpreted as DEPT.DEPT>200).

TIP: To validate that the sheet filter uses proper syntax, enable Developer Mode and then refresh the file. If the syntax is invalid, the error message will specifically identify the invalid sheet filter. Remember that if the sheet filter is built up using formulas or dependent on other data in the file, the filter may use valid syntax when the file is refreshed by one user but be invalid when refreshed by a different user. Files should be tested using a set of representative users to help catch these types of issues. For more information on developer mode, see Using Developer Mode.

See also