DeepSee MDX Reference
%ALL
 [Next]
   
Server:docs2
Instance:LATEST
User:UnknownUser
 
-
Go to:
Search:    

Enables you to use a member while ignoring any ROW and COLUMN context that uses the hierarchy to which this member belongs. This function is a DeepSee extension to MDX.
Returned Type
This function returns a member.
Syntax and Details
member_expression.%ALL
Where:
This function enables you to create calculated members that compare one member of a hierarchy to another member of the hierarchy (for example, comparing one product to all products).
Example
Sometimes it is necessary to compute values like the following:
For example, the following query uses a calculated member that equals the patient count for each age group, as a percentage of patients in all the age groups:
WITH MEMBER MEASURES.[pct age grps] AS 'aged.CURRENTMEMBER/aged.[all patients].%ALL', FORMAT_STRING='#.##' 
SELECT {MEASURES.[%COUNT],MEASURES.[pct age grps]} ON 0,
aged.h1.[age group].MEMBERS ON 1 FROM patients

                       Patient Count         pct age grps
1 0 to 29                       4,216                 0.42
2 30 to 59                      4,212                 0.42
3 60+                           1,572                 0.16
The calculated member is defined as the current member of the AgeD dimension, divided by the All member of that dimension:
aged.CURRENTMEMBER/aged.[all patients].%ALL
In contrast, consider the following query in which the calculated member does not use the %ALL function:
WITH MEMBER MEASURES.[BADpct age grps] AS 'aged.CURRENTMEMBER/aged.[all patients]', FORMAT_STRING='#.##' 
SELECT {MEASURES.[%COUNT],MEASURES.[BADpct age grps]} ON 0,
aged.h1.[age group].MEMBERS ON 1 FROM patients

                       Patient Count      BADpct age grps
1 0 to 29                       4,216                 1.00
2 30 to 59                      4,212                 1.00
3 60+                           1,572                 1.00
In this case, the value of aged.[all patients] in each row is the same as the value of aged.CURRENTMEMBER, because the row members belong to the same hierarchy as aged.[all patients].
Note that the %ALL function does consider the context given by members of other hierarchies. (It ignores only the hierarchy associated with the member that you use with the function.) For example:
WITH MEMBER MEASURES.[pct age grps] AS 'aged.CURRENTMEMBER/aged.[all patients].%ALL', FORMAT_STRING='#.##' 
SELECT CROSSJOIN(gend.MEMBERS,{MEASURES.[%COUNT],MEASURES.[pct age grps]}) ON 0,
aged.h1.[age group].MEMBERS ON 1 FROM patients

                  Patient Coun   pct age grps   Patient Coun   pct age grps
1 0 to 29                 1,985           0.39          2,231           0.45
2 30 to 59                2,123           0.42          2,089           0.42
3 60+                       926           0.18            646           0.13
Here, the first two Patient Count and pct age grps columns correspond to female patients and the second two correspond to male patients. Each pct age grps column indicates the patient count for that gender, as a percentage of all age groups for that gender.
Also note that the %ALL function does not ignore members of its hierarchy if those are used in a WHERE or FILTER clause; that is, the %ALL function fully respects all filtering applied to the query. For example:
WITH MEMBER MEASURES.[pct of all ages] AS 'aged.CURRENTMEMBER/aged.[all patients].%ALL', FORMAT_STRING='#.##' 
SELECT {MEASURES.[%COUNT],MEASURES.[pct of all ages]} ON 0,
aged.h1.[age group].MEMBERS ON 1 FROM patients 
WHERE aged.h1.[age group].[0 to 29]

                       Patient Count      pct of all ages
1 0 to 29                       4,216                 1.00
2 30 to 59                          *                    *
3 60+                               *                    *
See Also