InterSystems IRIS Data Platform 2019.2  /  InterSystems MDX Reference  /  Expression Types

InterSystems MDX Reference
Measure Search Expressions
Previous section           Next section
InterSystems: The power behind what matters   

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:
%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
The system parses a measure search expression as follows:
  1. %Search is treated as a dimension.
  2. Because the comparison expression is enclosed inside &[], the system 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 the system to do two things:

Previous section           Next section
Send us comments on this page
View this book as PDF   |  Download all PDFs
Copyright © 1997-2019 InterSystems Corporation, Cambridge, MA
Content Date/Time: 2019-10-14 06:57:36