%NOT
Returned Type
This function returns a member.
Syntax and Details
member_expression.%NOT
Where:
-
member_expression is a member identifier. (Note that you cannot use a general member expression.)
This function enables you to exclude the given member.
Example
Often it is necessary for the WHERE clause to exclude a single member. For example, first consider the following query, which uses EXCEPT:
SELECT aged.[age bucket].MEMBERS ON 1 FROM patients WHERE EXCEPT(aged.[age group].MEMBERS,aged.[age group].[0 to 29]) 1 0 to 9 * 2 10 to 19 * 3 20 to 29 * 4 30 to 39 166 5 40 to 49 139 6 50 to 59 106 7 60 to 69 86 8 70 to 79 62 9 80+ 41
You can use the %NOT function to rewrite the previous query as follows:
SELECT aged.[age bucket].MEMBERS ON 1 FROM patients WHERE aged.[age group].[0 to 29].%NOT 1 0 to 9 * 2 10 to 19 * 3 20 to 29 * 4 30 to 39 166 5 40 to 49 139 6 50 to 59 106 7 60 to 69 86 8 70 to 79 62 9 80+ 41
If you use this function on the column or row axis, you can see that it returns a member:
SELECT aged.[age group].[0 to 29].%NOT ON 1 FROM patients Not 0 to 29 600
As you can see the name of the member is NOT followed by the name of the excluded member.
The %NOT function provides several advantages:
-
The system does not need to materialize all the members of the level.
-
The negation occurs in an earlier part of the processing for greater efficiency.
-
%NOT returns a single member which can be combined (internally) with other filters to form simple tuple expressions.
