%NOT (MDX)
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.
