DeepSee MDX Reference
Measure Search Expressions
[Home] [Back] [Next]
InterSystems: The power behind what matters   
Class Reference   

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 DeepSee called %SEARCH:
%SEARCH.&[comparison expression]
Where comparison expression is a logical expression like the following example:
[MEASURES].[test score]>60
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:
You can use measure search expressions in all the following contexts:
Additional Notes
DeepSee parses a measure search expression as follows:
  1. %Search is treated as a dimension.
  2. Because the comparison expression is enclosed inside &[], DeepSee treats it as a KEY value, which permits it to contain arbitrary syntax.
  3. 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 DeepSee to do two things: