Skip to main content

%ALL

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 an InterSystems 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:

  • 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+                               *                    *

See Also

FeedbackOpens in a new tab