Query filters are conditions imposed on certain fields to restrict the result set of the query.
In general, fields defined as filters are related to dimensions in the multidimensional model so as to synchronize the queries made in the cube with the detailed result of the query to the database. In this case, the filter is associated to a determined level of the dimension of the multidimensional model. As you drill the cube, the detailed query is updated applying restrictions to the same values filtered in the O3 cube.
However, filters that are not related to dimensions can be defined, which lets you restrict the result of the query to the database, from the query pane itself. This can be useful in order to add relevant data not included in the multidimensional model and in those cases where information is analyzed in detail.
Defining the properties of Filters
The following picture shows a filter definition pane.
A filter is defined by the following fields:
- Name of the Field: It is a name you give to the Filter
- Argument Label: It is the label shown to the user as description of the filter in the Query.
- Argument Name: It identifies the filter internally. No blank spaces are accepted in its name
- Type of Argument: It is the type of data of the collection of possible values for the filter. For instance, a filter containing years as values, must be defined as Integer type.
- Dimension and Level: They let you choose the dimension and its level with which the filter is synchronized.
This relationship allows you to give the filter the value of the selected element of the dimension when you drill the cube.
The dimension you wish to associate must be chosen from the list of dimensions offered as field help.
To do so you need to have specified the cube for which the detailed query is defined in the query properties pane
See Defining Queries - Condition: It is an expression that states the condition applied to the field of the database to restrict the result set.
Syntax depends on the DBMS used.
The value assigned to the field is represented by a question mark ?. This is substituted by the value of the filter when the query is made.
For instance, the condition used to define the year as filter, from a date-type field in the database is: year(OrderDate) = ?
The example presented assumes the function year(date) that receives a date and returns an integer number representing its year is offered by the DBMS used. - Help: It lets you associate the list of possible values to the filter, and display them as field help in the detailed query pane.
To do so, you must define a query to the database containing the key and description of the filter. - Help Schema: It indicates the database connection that must be used for the query defined as filter help.
It is chosen from the list of previously defined connections.
Changes made in the definition of filters must be saved. In order to do this, use the "Save" button in the query properties pane.
To facilitate filter definition, you have the following operations:
- Add a new Filter
- Delete an existing Filter
- Move a Filter
- Modify and existing filter
To add a new filter you need to:
- Right-click on the "Filters" node
- Choose the "New Filter" option from the "Add" entry in that menu.
- Choose the filter to add with a left-click of the mouse and define its properties.
- Confirm changes with the "Save" button located in the queries properties pane.
To delete a filter do as follows:
- Right-click on the filter you wish to delete to display the operations menu over the filter.
- Choose the "Delete" option from that menu to delete the chosen filter.
- Confirm changes with the "Save" button located in the query properties pane.
The order in which filters are presented in the detailed query pane is determined by the position of the filters in the definitions pane. You may relocate a filter with the "Up" or "Down" options of the right-click drop-down menu.
To move a filter one position you have to:
- Right-click on the filter you wish to move to display the operations menu for the filter.
- Choose the "Up" or "Down" options in that menu to move the filter. This operation must be repeated as many times as positions you wish to move.
- Confirm changes with the "Save" button located in the query properties pane
To modify the properties of a filter do you need to:
- Select the Filter you wish to modify with a left-click of the mouse. In this way, its properties appear in the properties pane.
- Modify the properties you wish to change.
- Confirm changes with the "Save" button located in the query properties pane.