Database Reference
In-Depth Information
note that with the union() function, using the ALL keyword will retain duplicates.
Union({[Year].Members}, {[Year].Children}, ALL)
6.10.1.3 Querying for Descendants These examples show how to query descendants.
to save some trees, I will not output all the results because the results should be pretty
self-explanatory.
This query will return descendants, including the query member. note that this func-
tion works with any member, not just the dimension root, so you could use a member
that is in the middle of the hierarchy and return all descendants from that member down.
SELECT {} ON AXIS(0),
{Descendants([Year])} ON AXIS(1)
FROM [Sample.Basic];
This query returns descendants, excluding the query member.
SELECT {} ON AXIS(0),
Except({Descendants([Year])}, {[Year]}) ON AXIS(1)
FROM [Sample.Basic];
This query will only return level-1 descendants of the query member.
SELECT {} ON AXIS(0),
{Descendants([Year], 1)} ON AXIS(1)
FROM [Sample.Basic];
This query will only return level-0 descendants of the query member.
SELECT {} ON AXIS(0),
{Descendants([Year], Levels([Year],0))} ON AXIS(1)
FROM [Sample.Basic];
6.10.1.4 Querying for Ancestors This query allows you to query for ancestor members of
the query member, searching up two levels. take note that while the function requires
a layer or index, it is not necessary to know exactly how many levels are in the dimen-
sion. There is no upper level on the index, so if you simply want to return all ancestors,
you could use a number like 1000 instead of 2 in the index and still get the same results.
SELECT {} ON AXIS(0),
{Ancestors([Jan], 2)} ON AXIS(1)
FROM [Sample.Basic];
Axis-1
+-------------------
(Year)
(Qtr1)
(Jan)
This query searches for ancestor members of the specified member, up to one level.
Search WWH ::




Custom Search