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