More on filters

In Defining your first filter we added a new filter to a table using the AddNew method of the Table.Filters collection. You would typically create filters in this way when you want to specify a filter for use in a specific table. However, you will often find that you want to use the same filter for more than one table. The Table Object Model has a number of features to make this easy.

If you want to use the same filter on several tables, you can define the filter as a table document filter. You do this by creating it in the Document.Filters collection. The filter is then available to add to any table in the document. The following example creates a table document filter variable called Exclude11_16s and uses it on a specific table:

TableDoc.Filters.AddNew("Exclude11_16s", "Not age.ContainsAny({e1116_years})")
TableDoc.Tables.AddNew("Table1", "age * gender")
TableDoc.Tables[0].Filters.Add("Exclude11_16s")

This time we have used the Filters.Add method to add the filter to the table. You use this method to add a filter that has already been defined in the Document.Filters collection.

When you create a filter in this way, you need to explicitly add it to each table. However, you may sometimes want to add the same filter to all of your tables. The easiest way to do this is to define the filter as a global filter. You do this by creating the filter in the Document.Global.Filters collection. The filter is then applied automatically to all of the tables. If any of the tables already have a filter, the And operator is used to combine the global filter with the existing filters. Here is an example of creating a global filter:

However, you may sometimes want to add the same filter to some but not all of your tables. The Document.Default.Filters collection is useful in this situation. You may remember that in Handling zero values and setting hide rules, we saw that when you changed the value of the table document's default ZeroValueSymbol property, it did not affect tables that had already been created. Setting up a default filter works in a similar way.

Default filters are useful when, for example, you want to create two sets of tables, one of which is filtered on men and the other on women. Here's how you do it:

' Create a new default filter
TableDoc.Default.Filters.AddNew("Females", "gender = {female}")

' Create the second table
TableDoc.Tables.AddNew("Table2", "age * gender")

' Remove the Females default filter and create a new Males default filter
TableDoc.Default.Filters.Remove("Females")
TableDoc.Default.Filters.AddNew("Males", "gender = {male}")

' Create the third table
TableDoc.Tables.AddNew("Table3", "age * gender")

Note that when you create a filter using the Table Object Model, it is saved with the table document, but is not available to other table documents that use the same data set. If you want to create filters that become “permanently” part of a data set, you should set them up as Boolean variables in the metadata. See Example 3: Creating filter variables in the Data Management Scripting section for more information.