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]
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:
As the argument for the %FILTER clause
As the argument for the WHERE clause
As an argument for the FILTER function.
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.