Apply dashboard report table filters

You can apply filters to help control the data displayed in your reports. The available filters depend on whether the type of data dictionary underlying the report table is standard or SQL.

Filters for standard data dictionaries

For standard data dictionaries, there are three types of filters available.

  • Constant: Add a condition between a field and a constant, like in the WHERE clause of an SQL query.
  • Field: Add a condition between two fields, like in the WHERE clause of an SQL query.
  • Prompt: display a user prompt when visualizing a report to allow the user to change the filter's value.

To apply a filter for a standard data dictionary:

  1. Select the Filter tab.
  2. Modify the existing filter.
  3. If there are no filters available, click the Add button in the left upper corner of the screen.
  4. The Edit operand dialog box opens.
  5. Specify values for the fields.
  6. Field Description
    Field A list of fields selected in the Field section tab on which the filter will be applied.
    Value type Choose one of the following filter types:
    • Constant: add a condition between a field and a constant, like in the WHERE clause of a SQL query.
    • Field: add a condition between two fields, like in the WHERE clause of a SQL query.
    • Prompt: display a user prompt when visualizing a report to allow the user to change the filter's value.
    Operand This list contains the most frequently used operators (<, <=, =, >= and >)
    Field Value/Value
    • Field: A list of fields selected in the Field section tab used that you want to compared with the first field selected. This list is displayed only for the Field type filter.
    • Value(field): Enter the value of a constant. This field is displayed only for the Constant type filer.
    • Value (list): A list of prompt types:
      • Simple value: the user enters a value in the input box.
      • Unique selection value list: available values are displayed allowing the user to select one.
      • Multiple selection value list: available values are displayed allowing the user to select more than one.
      • Expression: filter based on an expression which is interpreted when the search is run. The following wildcards and patterns are supported:
        • *represents an unknown character string. For example, T* returns all values beginning with the letter T.
        • ? replaces one or zero unknown characters. For example, A?B returns AB and all three-character strings that begin with A and end with B.
        • "a phrase": quote marks can be used to specify a series of words taken together, failing which the words will be treated as different possible values for the filter.
        • - indicates that the pattern following the character is not permitted.
        • \ indicates that the following character must not be interpreted. For example, \? searches for a question mark, rather than "any single character". Similarly, to find a \ character, you must enter \\.
    Field name A unique identifier for the filter object.
    Description A user-friendly name to be shown in the report's filters.
    Use default value This option allows you to specify a default value for the filter.
    Default value You can specify a default value for the filter. In the case of a multiple selection list you can set several default values using "|" (for example, FR|UK|USA). If the field selected is a date or a time, you should use the calendar pop-up to select the default value.
    Mandatory Specify whether or not the filter is mandatory. If so, you should set a default value. When displaying a report, you will not be able to uncheck a filter which is mandatory.

    Visible

    It is possible to specify that a filter is not visible in the report, in which case, it is advisable to declare it as mandatory and set a default value.

    Hierarchical filter

    Hierarchical filters should be used when there are too many values in a selection list and the list can be reduced based on additional criteria.
    For example, if a selection list contains all the towns in England, it might be useful to filter the list by region first, and then by county. The resulting selection list would be easier to read. To have a hierarchical filer check the Hierarchical filter option and then click Add. The different options displayed should be completed: Description, Field, Use prompt, Data format, Sort. It is not mandatory to specify a Data format.

    Format

    You can specify a format for the filter. The different values of the selection list will be shown in that format. This option is available only for Unique and Multiple selection value list.
    Sort order This option allows you to sort Ascending or Descending the values in the selection list. This option is available only for Unique and Multiple selection value list.
    Complete list If you select this option, all values in the data base (including any values not returned by the query) are shown in selection lists of the filter.
  7. Click OK.
  8. The new filter is displayed on the Filter tab.
  9. (Optional) Add another operand to the new filter.
    1. Click the Add icon in the filter to display the Edit operand dialog box.
    2. Repeat steps 3 and 4.

Filters for SQL Data dictionaries

SQL filters are defined at the data dictionary level. For more details on how to create a filter, see Create an SQL query data structure.

This section concentrates on describing the filters customization at the report level.

The customization of the filters with SQL data dictionaries is very similar to filters with standard data dictionaries. It is possible to define the filter's Description and Default value, specify whether it is Visible in the report, and set one of the following Prompt types:

  • Simple value
  • Unique selection value list
  • Multiple selection value list
  • Expression

The Value list contains a list of data dictionaries that are the list values for the filter. For that it is necessary to create a separate data dictionary with o simple select of the column to obtain the list of values for the filter.

The value list option is enabled only for the Field and value type of filter defined at the data dictionary level.

Related Links