Database Reference
In-Depth Information
6.1.4 Navigation
The result of the query above contains aggregated values for all the years,
including the
All
column. If we wanted to display only the values for the
individual years (and not the
All
member), we would use the
CHILDREN
function instead as follows:
SELECT [Order Date].Year.CHILDREN ON COLUMNS, . . .
The attentive reader may wonder why the member
All
does not appear in the
rows
of the above result. The reason is that the expression
Customer.Country.MEMBERS
we used in the query is a shorthand notation for
Customer.Geography.Country.MEMBERS
and thus it selects the members of the
Country
level of the
Geography
hierarchy
of the
Customer
dimension. Since the
All
member is the topmost member of
the hierarchy, above the members of the
Continent
level, it is not a member
of the
Country
level and does not appear in the result. Let us explain this
further. As we have seen in Chap.
5
, every attribute of a dimension defines
an attribute hierarchy. Thus, there is an
All
member in each hierarchy of a
dimension, for both the user-defined hierarchies and the attribute hierarchies.
Since the dimension
Customer
has an attribute hierarchy
Company Name
,if
in the above query we use the expression
Customer.[Company Name].MEMBERS
the result will contain the
All
member, in addition to the names of all the
customers. Using
CHILDREN
instead will not show the
All
member.
Instead of taking the members of a dimension, a single member or an
enumeration of members of a dimension can be selected. An example is given
in the following query:
SELECT [Order Date].Year.MEMBERS ON COLUMNS,
{
Customer.Country.France,Customer.Country.Italy
}
ON ROWS
FROM Sales
WHERE Measures.[Sales Amount]
This expression queries the sales amount of customers by year summarized
for France and Italy. In the above query, the set in the row axis could be also
stated using expressions such as the following ones:
Customer.France
Customer.Geography.France
Customer.Geography.Country.France
Search WWH ::
Custom Search