%ALL (MDX)
Returned Type
This function returns a member.
Syntax and Details
member_expression.%ALL
Where:
-
member_expression is an expression that evaluates to a member.
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:
-
Percentage of one product compared to all products
-
Percentage of one product compared to another product
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+ * *
