Measure Search Expressions
This section describes how to create and use measure search expressions, which enable you to access rows from the fact table based on the value of a measure for the facts themselves (that is, at the lowest level rather than at an aggregate level). These expressions are an InterSystems extension to MDX.
A measure search expression has the following syntax, which refers to a special dimension in Business Intelligence called %SEARCH:
Where comparison expression is a logical expression like the following example:
Both sets of square brackets are required: the square brackets around the comparison expression and the square brackets of the measure identifier in the comparison expression. Hence a valid search expression always starts with %SEARCH.&[[
For example, the following query selects all patients with a test score higher than 60:
SELECT FROM patients WHERE %SEARCH.&[[MEASURES].[Test Score]>60] Result: 6,191
More generally, comparison expression can be a combination of logical expressions. This expression can include:
Logical comparison operators: > (greater than), >= (greater than or equal to), = (equal to), < (less than), and <= (less than or equal to).If the searchable measure contains string values, you can also use the SQL LIKE operator.
The AND operator, the OR operator, and parentheses to control precedence.
String literals enclosed in single quotes.
The SQL expressions IS NULL and IS NOT NULL. For example:
SELECT FROM HOLEFOODS WHERE [%Search].&[[Measures].[Units Sold] IS NULL]
You can use measure search expressions in all the following contexts:
The system parses a measure search expression as follows:
%Search is treated as a dimension.
Because the comparison expression is enclosed inside &, the system treats it as a KEY value, which permits it to contain arbitrary syntax.
The comparison expression is converted to an SQL statement against the fact table.
The preceding means that comparison expression can include SQL syntax.
Also, it may be possible to use a measure in a measure search expression even if it is not marked as searchable="true" in the cube definition. This attribute value causes the system to do two things:
Display this measure as an option in advanced filters.
Add additional index, if needed, to enable the measure to be searchable.