%FILTER Clause
Syntax and Details
select_statement %FILTER set_expression
Where:
-
select_statement is a statement that uses SELECT.
-
set_expression is an expression that returns a set of members or tuples.
Instead of set_expression, you can use a measure search expression; see the example to see the behavior of %FILTER in this case.
Because DeepSee MDX automatically converts types where appropriate, you can also use a single member expression or tuple expression in place of the set expression.
You can include as many %FILTER clauses as needed. This clause is particularly useful when you run queries programmatically, because it enables to you filter the results further by simply appending to the SELECT statement. (In contrast, if you use the WHERE clause and you need to add another filter item, it is necessary to rewrite the WHERE clause, because only one WHERE clause is permitted.)
Each set element is used as a separate slicer axis, and the results of all the slicer axes (of all %FILTER clauses) are aggregated together. This is the process of axis folding (a filter is considered to be a query axis). Axis folding means that if a given source record has a non-null result for each slicer axis, that record is counted multiple times.
In axis folding, values are combined according to the aggregation method for that measure, as specified in the cube definition. (In the examples here, %COUNT is added.)
For more details, see “Axis Folding” in the appendix “How the DeepSee Query Engine Works” in the DeepSee Implementation Guide.
Example
If you use the %FILTER clause with a measure search expression, the clause uses the rows of the fact table that do meet the given criteria. (A measure search expression is an InterSystems extension to MDX that considers the measure values in the fact table itself.)
SELECT MEASURES.[%COUNT] ON 0 FROM patients %FILTER %SEARCH.&[[MEASURES].[age]<10]
Patient Count
1,370
See Also
See the WHERE clause.