DeepSee MDX Reference
%NOT
[Back] [Next]
   
Server:docs1
Instance:LATEST
User:UnknownUser
 
-
Go to:
Search:    

Enables you to exclude a single member of a given level. This function is a DeepSee extension to MDX.
Returned Type
This function returns a member.
Syntax and Details
member_expression.%NOT
Where:
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:
See Also