DeepSee MDX Reference
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 a DeepSee extension to MDX.
A measure search expression
has the following
syntax, which refers to a special dimension in DeepSee called %SEARCH:
Both sets of square brackets are required: the square brackets
around the comparison expression and
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]
More generally, comparison expression
can be a combination of logical expressions. This expression can
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
As the argument for the %FILTER
As the argument for the WHERE
As an argument for the FILTER
DeepSee parses a measure search expression as follows:
%Search is treated as a dimension.
Because the comparison expression is enclosed inside &
, DeepSee 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.
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
Display this measure as an option in advanced filters.
Add additional index, if needed, to enable the measure
to be searchable.