AX3104

Applying a Quick Filter to a report

Using the Quick Filter feature, you can apply a temporary filter to a report. This allows you to quickly view the data at a different level of detail, without needing to alter the report configuration.

For example, you may be viewing an Income Statement report for the entire consolidated organization, and you want to view the same report at a different level of detail, such as for just North America or just the South region. You can use the Quick Filter to recalculate the report at the desired level of detail, and then clear the filter when you are done.

The Quick Filter is combined with your table security filters and any filters that are currently defined in the report, such as sheet filters and filters defined for Axiom queries.

This section discusses how end users can apply a Quick Filter to a report. If you are a report designer and want to learn how to set up a report for use with Quick Filter, see Setup considerations for using Quick Filter in a report.

To apply a Quick Filter to a report:

  1. On the Axiom tab, in the File Options group, click Quick Filter.

    NOTE: In systems with installed products, this feature may be located on the Main tab.

  2. At the top of the dialog, specify how the filter should be applied:
    • Workbook (default): The Quick Filter is applied to all sheets in the workbook.
    • Active Sheet: The Quick Filter is only applied to the currently active sheet.

    This selection may determine which hierarchies and tables are available in the dialog to build the filter. See Hierarchy and table availability in the Quick Filter dialog.

  3. In the Quick Filter dialog, define a filter using one of the following methods:

    • Data Hierarchies: Select the desired hierarchy levels(s) from the hierarchies listed in the dialog. As you select items in the hierarchy, the corresponding filter is automatically built in the Filter box.

      For example, you might have a hierarchy named Geography, which has local regions rolling up into countries, and countries rolling up into world regions. You can select the desired items that you want to see in the report, such as Europe, Asia, or North America as world regions. For more information and examples, see Understanding hierarchy-based Quick Filters.

    • Manual Filter: You can manually type a filter into the Filter box using standard filter criteria statement syntax. Fully qualified Table.Column syntax must be used.

    • Advanced Filter: Click Advanced Filter to create a filter using any reference table columns (not just hierarchy columns).

    Example Quick Filter dialog

    For more information about general filter settings in this dialog, see Using the Filter Wizard.

    NOTE: If the data in the report comes from tables that use multiple-level lookups, then using a hierarchy to apply a Quick Filter may not result in the intended data. Instead, you should use the Advanced Filter to build up the filter using the appropriate multiple-level syntax.

  4. Click OK.

    If the Quick Filter is applied to the entire workbook, a warning message informs you that the entire workbook will be refreshed. If you do not want to see this message again in the future, select Don't show this message again. Click OK to continue.

    If the Quick Filter is applied to the current sheet, that sheet is refreshed and no warning message appears.

If the file has been configured with GetCurrentValue("QuickFilter") functions, then these functions will display the currently applied Quick Filter for your reference. If not, you can view the current Quick Filter by clicking the Quick Filter button again. The current filter displays in the Filter box.

Clearing the Quick Filter

Once a Quick Filter has been applied to a report, the filter remains applied until one of the following occurs:

  • The file is closed. Quick Filters cannot be saved in the file and are always cleared when the file is closed.
  • A new Quick Filter is applied by using the Quick Filter button and selecting a different filter.
  • The Quick Filter is manually cleared. To clear the Quick Filter, click the Quick Filter button again and then click Clear Filter.

Hierarchy and table availability in the Quick Filter dialog

The hierarchies and tables shown in the Quick Filter dialog are based on the Axiom queries in the report. Axiom looks up the primary tables for the queries, and only shows the hierarchies and reference tables that are relevant to those primary tables. This is done to help ensure that the Quick Filter will be applicable to at least one query in the report.

If the filter applies to the entire workbook, then Axiom looks at the primary tables for all Axiom queries in the workbook. If the filter applies to the active sheet only, then Axiom looks at only the primary tables for the Axiom queries defined on the active sheet.

NOTE: In the Advanced Filter view, only reference tables are shown unless the primary table has potentially ambiguous lookup relationships. In that case, the primary data table is also shown so that the selections can be made directly on these lookup relationships, to avoid any ambiguity. For example, if the primary data table has columns PrimaryPhysician and SecondaryPhysician that both look up to Physician.Physician, then the selection must be made through the primary data table so that the correct path to Physician.Physician is used.

If the report uses GetData functions instead of an Axiom query, then all hierarchies and reference tables are listed in the dialog because Axiom cannot determine the "primary table" in this context. In this case, it is possible to define a Quick Filter that does not apply to any GetData functions in the workbook. If this occurs, the filter will simply have no effect.

Understanding hierarchy-based Quick Filters

When you use hierarchies to create a Quick Filter, Axiom automatically creates the filter based on your selections. When only one item it selected, the filter is simple—only data that matches the selected item is included. For example, if you select Asia from a Geography hierarchy, you will get a filter something like: Dept.WorldRegion='Asia'.

Simple Quick Filter

NOTE: Sometimes when you select a single "child" item underneath a "parent" item, the child and parent will be 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 Axiom constructs the filter as just Dept.Manager='Smith', that means all instances of Manager Smith are also under VP Jones.

You can select multiple items in the same hierarchy or from different hierarchies. Items from the same hierarchy are combined using OR, which means data matching any of the selected items is included. Items from different hierarchies are combined using AND, which means only data that matches both selected items is included.

Example 1

In example 1, we have selected two items from the same grouping level in a single hierarchy, so a simple filter criteria statement is created using IN. The resulting filter will include all data from Asia and Europe.

Example 2

In example 2, we have selected two items from different grouping levels, but within the same hierarchy. In this case a compound filter criteria statement is created using OR. The resulting filter will include all data that belongs to either Italy or US East.

Example 3

In example 3, we have selected two items from different hierarchies, so a compound filter criteria statement is created using AND. The resulting filter will include only data that belongs to both US East and VP David Prince.