Creating a Filter

Use this feature to return or include only a specific set of Members during the creation of a Dimension. This is similar to the concept of conditional retrieval.

1. From the Property Grid section of the Table window corresponding to a relational component, select the Filter option and click on the ellipsis button corresponding to it.

The Edit Filter dialog box appears, where you define/write your filter statement. This filter dialog includes icons that you can use to write the filter statement: Measure Columns, Equality Operators and Mathematical Operators.

2. Click OK after creation of the filter statement. This returns you to the Table window: the filter statement that you have just created will appear in the Property Grid.

Important

At the end of the process, Sort Order and Filter elements are appended to the end of the SQL statement that is executed against the relational database. In essence, the Sort Order is part of the Order By statement and the filter is part of the Where statement for the SQL clause. So, the syntax in the Sort Order and the Filter fields should be SQL Filter and Sort criteria-compliant.

For this example, the sample dim_Regions table is the relational table source. This table is composed of the columns Region, Continent and World. For this exercise, a filter statement will be created so that the Dimension will include only regions that indicate North America (as shown in a column in the Data Tab, these regions are Mexico, USA and Canada).

The following concern the Dimension attributes for the Region dimension:

• Set the DIMENSION MEMBERS to come from the Region column.

• Set the DIMENSION HIERARCHY whereby:
the FIRST Level or the ROOT Member is the World column; the SECOND Level or direct Children Member as the Continent column (comprised of the regions North America and South America); and the THIRD Level, which in this case is the LOWEST Level/ LEAF MEMBERS, as the Region column (contains the regions Mexico, Colombia, Venezuela, Brazil, Argentina, USA and Canada).

• Lastly, create a Filter statement that will only return the regions belonging to the North America continent.

To proceed with defining the Filter Statement:

• First, define the DIMENSION MEMBERS: Go to the Design Tab and select the Region column (first column), right-click and select Set As Dimension Member option.
For the Dimension Hierarchy, the accounts under this column will also serve as the Leaf Level Members, i.e., the Lowest Level Members.

• Next, define the HIERARCHY: in the Design Tab, select the World column (third column), right-click and select Define As Hierarchy Level then select the At Root option. Then select Continent column (second column); right-click and select Define As Hierarchy Level; then select Sub-Level of World option. OLATION will assign the Region column as LOWEST Level Member within the hierarchy.

• Next, define the FILTER STATEMENT.
To do this: Go to the Property Grid section of the Table window and locate the Filter option. Click on the ellipsis button (...) that appears on the far right of the grid.

• This opens the Edit Filter dialog box where you create the filter statement.
To create the filter statement that pulls Members only from the North America:

a. Click on the Measure Columns icon to return a list of all the column names that exist within the source relational table. Select the appropriate column, which is Continent.

b. Create a space by pressing the space bar.

c. Click on the Equality Operators icon. The drop-down shows the different operators that can be used for writing the filter statement. Select the equal sign (=) as operator.

d. Press the space bar to create space.

e. Enter "North America" enclosed in single quotes. When defining specific column Members in a filter statement, make sure that column Members are spelled accurately.

• Click the OK button. Back in the Property Grid of the Table window, note the defined Dimension attributes: Dimension Member (Region column), Hierarchy (World/Continent columns), and the filter statement (Continent = 'North America') are all displayed:

• Click the SAVE button.
To check that only Members belonging to the North America continent have been included in the Dimension: Go to the Database Explorer pane > Expand Dimensions > select then right-click on the Region dimension; then select View Hierarchy option: