AX1595

Using the Filter Wizard in an Axiom form

You can make the Filter Wizard available in an Axiom form, so that form users can open the wizard to create a filter criteria statement. The filter criteria statement gets submitted back to a target cell in the form source file, where it can then be used to impact the data shown in the form. For example, you might reference the filter in the Data Filter setting of a particular Axiom query, or as a Sheet Filter to impact all queries on a particular sheet.

To do this, use the Filter Wizard command on a Button component (or on a Button tag in a thematic Formatted Grid component). Form users can click on the button to open the Filter Wizard and build the filter criteria statement. When configuring the command, you can specify the user interface for creating the filter—either Advanced mode or Hierarchy mode—and you can optionally limit the wizard to only showing certain tables or certain hierarchies.

You can also use the Filter Wizard command to allow users to create a limit query statement for an Axiom query, but this is an advanced feature only for use in certain special situations.

NOTE: The Filter Wizard command requires the Web Client Container to be enabled for the form. If it is not enabled, an error will occur when the command is executed. The Web Client Container is enabled by default for new forms.

Setting up a button to open the Filter Wizard

To allow form users to open the Filter Wizard and create a filter, use a Button component that is configured to run the Filter Wizard command.

To start off, add the Button component to the Axiom form canvas and then configure the button properties as desired. The button text should be set to something like "Define Filter" or "Filter Data". You can then configure the Command for the button as follows:

  1. In the component properties, click the [...] button to the right of the Command box.

  2. In the Shortcut Properties dialog, click the [...] button to the right of the Shortcut Target box.

  3. In the Axiom Explorer dialog, expand the Command Library and then locate the Filter Wizard command in the library. Select the command and then click Open.

    In the Shortcut Properties dialog, the Filter Wizard command is now listed as the shortcut target, and the relevant shortcut parameters are now available.

  4. Complete the shortcut parameters for the command as follows, and then click OK to close the Shortcut Properties dialog.

    Item Description

    Target Cell

    The target cell in the source file to place the filter after it has been created (such as Sheet1!A1).

    Type

    Select one of the following to determine how users can create the filter:

    • Advanced Filter (default): Users can create the filter based on literal table column names and values. The Advanced View option also supports creating limit query statements for use in Axiom queries.

    • Hierarchy Filter: Users can create the filter based on hierarchy levels in defined hierarchies.

    Tables / Hierarchies

    Optional. Specify one or more tables or hierarchies to limit the options shown in the Filter Wizard. Valid entries depend on the specified filter type:

    • If using Advanced Filter, then you can enter one or more table names, separated by commas. The Filter Wizard will only show columns from the specified tables, as well as any lookup tables.

      For example, if you list GL2019, and that table has lookups to Acct and Dept, then all three tables will be available in the wizard.

    • If using Hierarchy Filter, then several different options are available to specify the hierarchies to be shown. See the discussion following this table for more information.

    If left blank, then the Filter Wizard shows all available options for the specified filter type. For Advanced Filter this means all tables, and for Hierarchy Filter this means all hierarchies.

    NOTE: If desired, you can read the list of tables or hierarchies from a designated cell in the source file instead of entering the list into the shortcut parameters. To do this, use a cell reference enclosed in brackets, such as: [Sheet1!A1]. When using this approach, you can dynamically change the list by using a formula in the specified cell.
    Limit Filter Column

    Optional. In most cases, you will leave this property blank. Only complete this property if you want to use the Filter Wizard to create a limit query statement for an Axiom query. For more information on completing this property, see Filter Wizard command.

    Processing Step

    Specifies when the command will be executed during the Axiom form update process. This option is set to Form - After Updating Values and cannot be changed. For more information, see Timing of command execution.

    NOTE: This command has special behavior and does not follow the normal form update process. For more information, see Form update behavior when using the Filter Wizard.

    When using the Hierarchy Filter option, the following entries are valid in the Tables / Hierarchies parameter.

    • Enter a table name to display all hierarchies defined for that table. For example, enter DEPT to display all hierarchies defined on the DEPT table.

      You can also enter multiple table names, separated by commas. The dialog will display all hierarchies defined for all listed tables.

    • Enter Table:HierarchyName to only show the specified hierarchy. For example, DEPT:Geography to only show the Geography hierarchy on the DEPT table.

      You can also enter multiple table:hierarchy pairs, separated by commas. The dialog will display all specified hierarchies.

    • Enter Table.Column:HierarchyName to only show the specified hierarchy and also use the specified Table.Column in the resulting filter criteria statement. For example, DEPT.Region:Region to show the Region hierarchy on the Region table, where DEPT.Region looks up to the Region table. The resulting filter criteria statement will be written such as Dept.Region.RegionType=1 instead of Region.RegionType=1, thereby allowing the filter to be applied to tables with a lookup to DEPT.

    Example Shortcut Properties dialog

Using a Button tag in a Formatted Grid component

Button tags in thematic Formatted Grid components can also be configured to run this command. In this case, use the Command parameter within the tag to assign the command to the button. The easiest way to do this is to use the Tag Editor dialog or the Data Source Assistant to create the tag and edit the tag parameters. When using these helper dialogs, you can select the command and configure the shortcut parameters using the same method described previously for the Button component.

When using a Button tag, you can optionally specify the Target Cell using just a column letter, instead of a full cell reference. For example, you can specify J to indicate that the target cell should be in column J in the current row of the grid.

This column letter syntax can also be used for the Tables / Hierarchies parameter, if you want to read the list from the sheet instead of entering it in the shortcut parameters. In this case, enter the column letter in brackets, such as [K].

Example column letter entries that are valid for use with Button tags

Form update behavior when using the Filter Wizard

When using the Filter Wizard command on a button, the full form update process does not apply. Instead, the following occurs when a user clicks the button:

  1. The form update process proceeds as normal until it reaches the After Updating Values processing step, then it is aborted.

    Due to this abbreviated update process, the button cannot use Save on Submit, and cannot execute any other commands at later processing steps.

  2. The Filter Wizard dialog opens and the user creates a filter.

  3.  If the user clicks OK on the Filter Wizard, the dialog is closed and the following occurs:

    • The filter is submitted back to the source file, to the designated target cell.

    • The source file is calculated and refreshed (including running Axiom queries set to Refresh on Manual Refresh).

    • The form web page is updated to show the latest data.

    NOTE: Clicking OK in the Filter Wizard does not clear or change the triggering component. The triggering component will still be recorded as the button that opened the Filter Wizard. Therefore if you want to dynamically enable or disable Axiom queries for the data refresh, you can base the formulas on the button with the Filter Wizard command.

  4. If the user clicks Cancel on the Filter Wizard (or clicks the close button in the top right corner), then the dialog is closed and no further actions occur. The form data is not refreshed and the form web page is not updated. Although interactive values were submitted at the start of the process, you will not see the effects of any changes until the form is updated using another component.

User experience

In Axiom forms, you configure the wizard to use either the Advanced Filter view or the Hierarchy Filter view (known as Simple View in the Desktop Client). It is not possible for the user to switch between views—only the specified view is available in the wizard.

When using Advanced Filter view, users can create a filter based on literal table columns and values in those columns. The dialog shows only the tables listed in the Tables / Hierarchies shortcut parameter (as well as their lookup tables). To create a filter, select a table column, select an operator, and select one or more values.

Example Advanced Filter view

In Advanced Filter view, users also have the option to load saved filters from the Filter Library, and save filters to the Filter Library for future use.

When using Hierarchy Filter view, users can create a filter based on selections in one or more hierarchies. The dialog will either show all hierarchies, or only the hierarchies listed in the Tables / Hierarchies shortcut parameter. To create a filter, select one or more items. A filter criteria statement is automatically created based on the selected items.

Example Hierarchy Filter view

When using either view, the filter is initially built out in the Preview box. Click the Apply button underneath the Preview box in order to copy the filter down to the Filter box. Clicking the OK button then closes the dialog and sends that filter to the form source file. If the OK button is clicked when the Filter box is blank, then a blank filter will be submitted.

When using the Advanced Filter view, compound statements can be created as follows:

  • Create one statement, then click Apply to move the statement down to the Filter box.
  • Create a second statement, then click OR or AND to combine the second statement with the first statement using the specified operator. Repeat as needed.

When using the Hierarchy Filter view, the Preview and Filter boxes just provide a means to preview the newly created filter as compared to the current filter (if any). The Hierarchy Filter view cannot be used to create compound statements.

NOTE: In both views, it is possible to manually edit the filter criteria statement, which may result in invalid filters. An invalid filter will cause an error in the form if the filter is being applied to data queries. In this case the user must use the Filter Wizard again to define a new, valid filter (including blank for no filter), or reload the form to start over.

If a Limit Filter Column is specified with Advanced Filter view, then the dialog changes to a two-step process that allows the user to make selections to generate a limit query statement. In this case, the user experience is the same as when using an AdvancedFilter refresh variable or the ShowFilterWizardDialog function to create a limit query statement.

Design considerations

  • If you want the Filter Wizard to start off with a filter, you can enter a filter criteria statement into the designated target cell for the Filter Wizard command. When a user launches the Filter Wizard for the first time, this starting value will display in the Filter box. The user can then create a new filter and overwrite your starting filter.

  • The Filter Wizard allows the user to submit a blank filter. Make sure that your file is configured to handle a blank result without error.

  • The list of available tables or hierarchies in the wizard is not automatically filtered based on the queries in the file. Depending on how the filter is being used in the form source file, and depending on the tables or hierarchies available in the dialog, the user may create a filter that has valid syntax but is still invalid in the context of its eventual use case. You should define the Tables / Hierarchies parameter as narrowly as possible to help avoid the possibility of query errors due to invalid filters.