Using the Filter Wizard

For example, you may have a hierarchy for Geography that starts at the WorldRegion level, then goes down to the Country level, and then goes down to the LocalRegion level. If you want to filter by a particular country in the Asia WorldRegion, you can expand the Geography hierarchy, then expand the Asia WorldRegion, and then select the desired country.

The hierarchies available to you are defined by your system administrator, based on grouping columns in reference tables. If your system has no defined hierarchies (or if no defined hierarchies are relevant to the current context), then the Data Hierarchies section does not display, and the Advanced Filter opens directly.

Note the following about filters created using data hierarchies:

• Only include filter criteria statements can be created using data hierarchy selections. As you select items, those items will be included by using an equals (=) operator or an IN statement (for including multiple items at the same level). If you want to write a filter criteria statement that specifies items to exclude, or that uses other operators such as greater than or less than, then you must use the Advanced Filter.
• Certain assumptions are made regarding the use of AND and OR when multiple items are selected from different hierarchy levels or different hierarchies. If you want to change the way each statement is joined, you can manually edit the filter in the Filter box, or you can use the Advanced Filter.
• Sometimes when you select a child item underneath a parent item, the child and parent are joined with AND. For example: DEPT.VP='Jones' AND DEPT.Manager='Smith'. This means that the DEPT table has other instances of Manager Smith that belong to different VPs, so the compound statement is to ensure that you only get the data where Manager Smith is under VP Jones. You can manually edit the filter to remove the Jones portion of the statement if you want to see all data for Manager Smith, regardless of VP. If instead the system constructs the filter as just Dept.Manager='Smith', that means all instances of Manager Smith are also under VP Jones.

To create filters using data hierarchies:

1. In the right side of the dialog, click Simple Filter.

   

2. Select the checkbox for each item to include in the filter. You can expand each hierarchy to see the items listed in it. You can also type a value into the filter box above the hierarchies to filter the list. As you select items, the filter criteria statement is created in the Filter field at the bottom of the dialog.

   

3. Do one of the following:
   • To apply the filter as is, click OK.
   • To manually edit the filter, type in the Filter field, and click OK.

Using the Advanced Filter option, you can create a filter using any relevant table and column, and using any supported operator.

To create a filter using Advanced Filter:

1. In the right side of the dialog, click Advanced Filter.

   

2. In the left-hand side of the dialog, select the table column on which you want to base the filter.

   For example, to create a filter such as DEPT.DEPT>=5000, then you must select the DEPT column from the DEPT table.

   To find the desired table and column, do the following:

   • Use the View by option to view the list by table, table type, folder, or alias. To select an alias, you must change the view to Alias—aliases are not listed under their assigned table.
   • You can also filter the list by typing into the filter field. The filter matches based on table name or column name.

   After you select a table column, the values in that column display in the right-hand side of the dialog.

   NOTE: If the selected column is a key column for a data table, and that key column links to a lookup column, the Filter Wizard automatically uses the lookup column in the reference table instead of the column in the data table. This is the recommended way to build the filter throughout the system, and it is required in some contexts. For example, if you select the column Acct in the GL2019 data table, the filter wizard automatically uses ACCT.ACCT in the filter (instead of GL2019.ACCT).

3. In the right-hand side of the dialog, type or select the value on which to base the filter.

   

   You can type into the field above the list of values to filter the list or to specify a value. If one or more values are selected, then those items are used in the filter. Otherwise, whatever is typed into the field is used by the filter.

   If the column is a string, you can type an asterisk at the front or end of the value if you want to use "ends with" or "begins with" wildcard matching.

4. In the space between the two selection boxes, select the operator to use for the filter criteria statement, such as equals, not equals, greater than, or less than. By default, the filter statement uses equals (=).

   

   Note the following about filter operators:

   • Greater than / less than options are only available if the column data type holds numbers or dates.
   • If multiple items are selected, then IN and NOT IN syntax is automatically used for equals and not equals respectively. Note that if the operator is equals but you select more items than you have not selected, the system will instead use NOT IN syntax for the unselected items to simplify the filter statement.
   • If the column is a string column, and you type a value rather than selecting it, then LIKE and NOT LIKE syntax is automatically used for equals and not equals respectively. By default, wildcard characters (% signs) are placed on both sides of the text, meaning that it will match any value that contains the text. If you place an asterisk to the start or end of the text, then the wildcard character will be only at that location.

   • If the column is a string column and the value contains an apostrophe (such as O'Connor), the wizard automatically converts this value to double apostrophes so that it is valid for use in the filter (O''Connor). Apostrophes in string values must be escaped this way so that they are not interpreted as the closing apostrophe for the filter criteria statement.

5. Review the filter criteria statement in the Preview box to ensure that it is as intended. If you need to make changes, edit your selections made above. The Preview box is not editable.

6. Do one of the following:

   • If the filter criteria statement is finished, click OK. The Filter Wizard uses the statement in the Preview box (you do not have to click Apply in this case).

   • To create a compound filter, click Apply to move the current criteria statement into the Filter box. Then, repeat steps 1-4 to create another criteria statement. When the next statement is complete, click AND or OR to join it to the prior statement.

     You can repeat this process as many times as necessary to create the desired statement. You can also edit the full criteria statement within the Filter box as needed. When the entire statement is complete, click OK.

If a filter already exists in the setting or cell from which you launched the Filter Wizard, that existing filter displays in the Filter field of the wizard.

Note the following:

• If you select a new item from the Data Hierarchies section, the new filter will overwrite the existing filter in all cases.

• If you build a new filter using the Advanced Filter, you can concatenate that filter to the existing filter using AND or OR. If you would rather replace the existing filter, then click the Delete icon to clear the existing filter from the Filter field, and then accept the new filter.

Whenever possible, the Filter Wizard is context-sensitive, meaning that it only displays hierarchies and tables that are relevant to the current context. For example, if you are defining a filter for a file group permission set in Security, the Filter Wizard is limited to the plan code table (and any hierarchies defined for that table).

The available tables and columns in the Filter Wizard are also subject to the following settings:

• Security – If a you do not have any read access to a table, then that table does not display in the Filter Wizard. If you have filtered read access to a table, then the filter is applied to the values displayed in the wizard.
• Column Properties – Individual columns in a table can be configured to be hidden in the Filter Wizard using the Is Filter Column setting. This may be used to hide columns that are unlikely to be used in filters. Filters can still be manually created using these columns; the property simply hides the column from the user interface to streamline the column list.