DeepSee MDX Reference
Measure Search Expressions

 Server:docs2 Instance:LATEST User:UnknownUser

 [ Home ]  >  [ InterSystems DeepSee ]  >  [ DeepSee MDX Reference ]  >  [ Expression Types ]  >  [ Measure Search Expressions ] Go to: Details Uses Additional Notes Search:

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 a DeepSee extension to MDX.
Details
A measure search expression has the following syntax, which refers to a special dimension in DeepSee called %SEARCH:
`%SEARCH.&[comparison expression]`
Where comparison expression is a logical expression like the following example:
`[MEASURES].[test score]>60`
Note:
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.
• Numeric literals.
• 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]`
Uses
You can use measure search expressions in all the following contexts:
• As the argument for the %FILTER clause
• As the argument for the WHERE clause
• As an argument for the FILTER function.