%FILTER Clause (MDX)

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 InterSystems 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.)

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

%SQLRESTRICT

If you have enabled the %SQLRESTRICT dimension for a cube, you can include an SQL SELECT statement or WHERE clause in your MDX queries against that cube. This will allow run-time restriction via an SQL statement in your MDX query. To use the %SQLRESTRICT dimension, add a %FILTER clause using the following syntax:

%FILTER %SQLRESTRICT.&[sqlStatement]

where sqlStatement is an SQL SELECT statement or an SQL WHERE clause. Any SELECT statement must be designed to return a of list of IDs from the source table of the cube. These IDs limit which facts may be considered in the final MDX result — only facts that correspond to records with an ID in the returned list of IDs are included in the final result. Consider the following example MDX query that uses a SELECT statement for sqlStatement:

SELECT FROM HOLEFOODS 
WHERE [Comments].[H1].[Comments].&[SELECT ID FROM HoleFoods.SalesTransaction WHERE ID IN (1,2,3,4)]

It is also possible to query an external reference table. For example, given the following simplistic tables:

the following %SQLRESTRICT subquery would return PatientID values of 1 and 6, leaving only Bill and Alice, who have IDs of 1 and 6 in the Example Cube Source Table, as possible results for your MDX query:

%SQLRESTRICT.&[SELECT PatientID From Cohort.Table WHERE Age < 35]

Note that the above sample tables are overly simplistic and are for example purposes only.

The %SQLRESTRICT subquery should return a single column of numbers, which are assumed to IDs from the source table.

Alternatively, a WHERE clause may be used as a shortcut to describe a limitation of the source records directly. Consider the following examples:

SELECT FROM HOLEFOODS %FILTER %SQLRESTRICT.&[WHERE ID IN (1,2,3,5,10)]

SELECT FROM HOLEFOODS %FILTER %SQLRESTRICT.&[WHERE Outlet = 24]

SELECT NON EMPTY [DateOfSale].[Actual].[YearSold].Members ON 1 
FROM [HOLEFOODS] %FILTER %SQLRESTRICT.&[WHERE YEAR(DateOfSale)='2017']

Internally, this WHERE clause syntax causes the system to insert a SELECT statement into the %SQLRESTRICT subquery, with the target being the source class of the cube that is being queried in the MDX query. For example, these two queries are equivalent, because Holefoods.SalesTransaction is the source class of the Holefoods cube:

SELECT FROM HOLEFOODS %FILTER %SQLRESTRICT.&[SELECT ID FROM Holefoods.SalesTransaction WHERE ID IN (1,2,3,4)]

SELECT FROM HOLEFOODS %FILTER %SQLRESTRICT.&[WHERE ID IN (1,2,3,4)]

See Also

See the WHERE clause.