PreviousNext

Filtering Hierarchical Data

This topic provides a number of examples to illustrate how filters and expressions work when you are using the hierarchical view of the data. The examples are based on the Household XML sample. See the topic The Household Sample for more information.

The first table is an unfiltered table at the Person level:

Here is the table:

Now let's add a filter to the table to select females only. We do this by creating a person-level filter, because the gender variable is at the Person level.

To specify the level of a filter, click the Level button in the Filter tab.

Now suppose you also want to create a global filter based on the top-level numrooms variable to select households that have less than eight rooms. Because this variable is at the top level, we will create the global filter at the top level. Note that the level of a new filter automatically defaults to the top level, so you would not need to set the level explicitly.

Here is the table after applying both filters:

When you specify multiple separate filters like this, IBM® SPSS® Data Collection Survey Reporter automatically down-levs the expressions to the level of the lowest table filter and combines the resulting expressions using the And operator.

Adding advanced filters using the Filter Syntax pane

When you create a filter, all of the variables in the filter expression must be at the level of the filter. However, you can include variables from a higher parent level by down-leving them. For example, you can create a single Person-level filter that is identical to the combined separate Person-level and Top-level filters described above by adding the NumRooms variable to the Person-level filter. When you use the Filter tab, Survey Reporter automatically down-levs the NumRooms variable to the Person level if the level of the filter is defined as the Person level. If you display the Filter Syntaxpane, you will see the filter expression is as follows:

gender.ContainsAny({Female}) AND ^.numrooms<8

The numrooms variable is preceded by the down-lev operator (^.). This filter selects women and girls who live in households that have less than eight rooms, just as the combination of the two separate filters does.

You can include variables from a lower child level in the Filter Syntax pane by using the syntax for up-leving them. For example, if we wanted to create our filter at the top level, we could up-lev the Gender variable. However, it is not possible to create the previous filter at the top level. We can either select households that contain at least one female and less than eight rooms:

SUM(person.(gender.ContainsAny({Female}))) AND numrooms<8

which gives this table:

Or we can select households that contain no males and less than eight rooms:

Sum(Person.(Gender = {Male})) = 0 And NumRooms < 8

which gives this table:

However, it is not possible to create a top-level filter to select females only, because that information is not available when you up-lev the data to the household level. The reason for this is that all of the Person-level data for each household is collapsed together.

The syntax for up-leving data is the up-lev operator ( .( ) in combination with one of the aggregate functions supported by the Data Model (Sum in this example).

When creating multiple filters at different levels for individual tables, the filter levels must have a direct parent-child relationship with each other and not be parallel to each other (on different branches of the tree). For example, using the Household sample data, you cannot create separate filters at the Person and the Vehicle levels for the same table, because these levels are parallel to each other. However, you can create separate filters for the same table at the Person level and Trip levels, because the Trip level is a direct descendent of the Person level.

If you need to filter a table on variables from parallel levels, you must create the filter at the first common ancestor level and up-lev the variables from one of the levels to the first common ancestor level and then down-lev the data to the level of the filter. If you are working in the Filter tab, you simply need to set the level of the filter and then select the variables, and Survey Reporter takes care of the up-leving and down-leving for you.

However, when you are working in the Filter Syntax tab, you need to specify the expression correctly. Here is an expression for a Person-level filter that uses the vehicletype variable from the parallel Vehicle-level:

Gender = {Male} And ^.Sum(Vehicle.(VehicleType = {Motorbike})) > 0