An axis specification consists of a set and one or more axis keywords.
<axis_specification> :: = [NON EMPTY] <set> ON COLUMNS|ROWS|PAGES|CHAPTERS|SECTIONS|AXIS(<unsigned_integer>)
Understanding the following concepts will help you construct axis specifications for many SELECT queries
If providing multiple axes, you cannot skip axes. For example, you can specify a Row axis only if you have a Column axis. You can specify a Pages axis only if you also have Column and Row axes.
You can also use ordinals to represent the axes. For example, you can specify <set> ON AXIS(0), <set> ON AXIS(1), etc.
You can specify up to 64 axes (though it is common to use just two). The first five ordinal axes have keyword aliases:
Axis Keyword | Axis Ordinal |
---|---|
COLUMNS | AXIS(0) (default if nothing specified) |
ROWS | AXIS(1) |
PAGES | AXIS(2) |
CHAPTERS | AXIS(3) |
SECTIONS | AXIS(4) |
For example:
SELECT set1 ON COLUMNS, set2 ON ROWS FROM Sample.Basic
is the same as:
SELECT set1 ON AXIS(0), set2 ON AXIS(1) FROM Sample.Basic
Both return a hypothetical data cube (or subset) of the following format:
(axis) | Member names in set1 |
---|---|
Member names in set2 | Data at intersections of set1 and set2 members |
The examples above are hypothetical because they will not return a cube until values are provided for the sets. In the following example, we replace set1 and set2 with real sets:
SELECT {[100-10], [100-20]} ON COLUMNS, {[Qtr1], [Qtr2], [Qtr3], [Qtr4]} ON ROWS FROM Sample.Basic
which returns the following results:
(axis) | 100-10 | 100-20 |
---|---|---|
Qtr1 | 5096 | 1359 |
Qtr2 | 5892 | 1534 |
Qtr3 | 6583 | 1528 |
Qtr4 | 5206 | 1287 |
You can represent the sets in each axis in many ways.
SELECT { } ON COLUMNS from sample.basic
illustrates that you can choose nothing for a set. However, no cell values will be returned. The following rules apply:
When any of the axes contains an empty set, no cell values are returned. The axes whose sets have at least one tuple will have their tuples returned.
If there are no axes at all, then exactly one cell is returned using the default member of each dimension. The slicer tuple, if present, overrides the default member for the respective dimensions.
SELECT { ( [Year].[Qtr2] ) } ON COLUMNS from sample.basic
illustrates using a set that contains a single tuple.
For more information about sets, see MDX Set Specification.
The axis specification syntax including NON EMPTY is shown below:
<axis_specification> ::=
[NON EMPTY] <set> ON
COLUMNS | ROWS | PAGES | CHAPTERS |
SECTIONS | AXIS (<unsigned_integer>)
Including the optional keywords NON EMPTY before the set specification in an axis causes suppression of slices in that axis that would contain entirely #MISSING values.
For any given tuple on an axis (such as (Qtr1, Actual)), a slice consists of the cells arising from combining this tuple with all tuples of all other axes. If all of these cell values are #MISSING, the NON EMPTY keyword causes the tuple to be eliminated.
For example, if even one value in a row is not empty, the entire row is returned. Including NON EMPTY at the beginning of the row axis specification would eliminate the following row slice from the set returned by a query:
Qtr1 | |||||
---|---|---|---|---|---|
Actual | #Missing | #Missing | #Missing | #Missing | #Missing |
For another example, see the Tail function.
A property, in MDX grammar, refers to the Essbase concepts of attributes and UDAs.
The axis specification syntax including the properties specification is shown below:
<axis_specification> ::=
[NON EMPTY] <set> [<dim_props>] ON
COLUMNS | ROWS | PAGES | CHAPTERS |
SECTIONS | AXIS (<unsigned_integer>)
As shown in the above syntax, a properties specification can follow the set specification in an axis.
For more information about properties, see About MDX Properties.