Set Value Expressions

For any expression examples in the following sections, SampleSet is defined as

{USA, Buffalo, France, NYC, London, California, LA, Nice, UK, Paris}

with sales data as indicated in the following table.

<dimension>.MEMBERS

The set of all members in <dimension>.

For example,

Geography.MEMBERS

is the set of all members in the Geography dimension.

<hierarchy>.MEMBERS

The set of all members in <hierarchy>.

For example,

Time.Quarterly.MEMBERS

is the set of all members in the Quarters hierarchy of the Time dimension.

<level>.MEMBERS

All members at a given level in a dimension.

<member>.CHILDREN

All children of <member>.

ANCESTOR(<member>, <distance>)

Returns the ancestor of <member> that is <distance> steps away in the hierarchy. If <member> has multiple parents, the provider elects which parent member to return.

ANCESTOR(<member>, 0) returns <member>.

ANCESTORS(<member>, <level>)

Returns all ancestors of <member> at the specified level. Unlike the ANCESTOR function, ANCESTORS is a set value expression. Useful in network hierarchies where a member may have more than one parent.

The set of returned members must all be from the same hierarchy, but <level> need not be a level of the same hierarchy as <member>.

ANCESTORS(<member>, <distance>)

Returns all members of the hierarchy that are exactly <distance> steps above <member> in the hierarchy. This form of ANCESTORS is intended for cases in which the level of the parent is unknown or cannot be named.

Unlike the ANCESTOR function, ANCESTORS is a set value expression. It is useful in network hierarchies where a member may have more than one parent.

The set of returned members must all be from the same hierarchy.

CROSSJOIN(<set1>, <set2>)

Generates the cross-product of the input sets. The order of tuples in the resulting set is as follows:

If <set1> = {x1, x2,...,xn} and <set2> = {y1, y2, ..., yn}, then CROSSJOIN(<set1>, <set2>) is

{(x1, y1), (x1, y2),...,(x1, yn), (x2, y1), (x2, y2),...,
(x2, yn),..., (xn, y1), (xn, y2),..., (xn, yn)}

DESCENDANTS(<member>, <level> [, <desc_flags>])

All descendants of <member> at <level>. By default, only members at the specified level will be included. This corresponds to a desc_flag value of SELF. By changing the value of desc_flag, the consumer can include or exclude descendants at <level>, the children before <level>, and the children after <level> (until the leaf).

For example,

DESCENDANTS(USA, Counties)

yields all counties in USA and is the same as

DESCENDANTS(USA, Counties, SELF)

The statement

DESCENDANTS(USA, Counties, BEFORE)

yields all states in USA. If another level exists between Countries and States, members from that level will also be returned.

And the statement

DESCENDANTS(USA, Counties, AFTER)

yields all cities in USA. If another level exists after Cities, members from that level will also be returned.

The statement

DESCENDANTS(USA, Counties, BEFORE_AND_AFTER

yields all states and cities in USA.

DESCENDANTS(<member>, <distance> [, <desc_flags>])

All descendants of <member> that are <distance> steps away from <member>. Otherwise identical to the original form of DESCENDANTS, it is useful when dealing with ragged hierarchies.

For example,

DESCENDANTS(USA, 1)

yields all states in the USA, while the statement

DESCENDANTS(ISRAEL, 1)

might return all cities in Israel.

The use of <desc_flags> is the same as for the original form of DESCENDANTS. For example, the statement

DESCENDANTS(USA, 2, BEFORE)

would also return all states in the USA.

The LEAVES flag can be used to return all the leaf-level descendants of the given member, up to <distance> steps away. In a Geography dimension, the statement

DESCENDANTS(Geography.[All], 4, LEAVES)

might return all cities in the hierarchy, or possibly counties or states if those members contain no children.

DESCENDANTS(<member>, 0, LEAVES) would return <member>.

DISTINCT(<set>)

Deletes duplicate tuples from a set. Duplicates are deleted from the tail.

EXCEPT(<set1>, <set2> [,[ALL]])

Finds the difference between two sets. Duplicates are eliminated from both sets prior to finding the difference. The optional ALL flag retains duplicates. Matching duplicates in <set1> are eliminated, and nonmatching duplicates are retained.

EXTRACT(<set>, <dimension>[, <dimension>...])

This is the opposite of the CROSSJOIN function. The resulting set consists of tuples from the extracted <dimension> elements. This function always removes the duplicates.

FILTER(<set>, <search_condition>)

Filters set based on <search_condition>.

For the expression

FILTER(SampleSet, (Sales,[1996]) < (Sales, [1995]))

the following result is returned:

{UK, Paris, Buffalo}

GENERATE(<set1>, <set2> [,[ALL]])

This is best explained with an example, as follows:

GENERATE({USA, France}, DESCENDANTS(Geography.CURRENTMEMBER, Cities))

The GENERATE function will repeatedly go through for each member of the set {USA, France} and apply the expression DESCENDANTS(Geography.CURRENTMEMBER, Cities). Each such application results in a set. (Application to USA will generate the set of all cities in USA; application to France will generate all cities in France.) These sets are joined by union to get the result of this function. In this example, all cities in USA and France will be the result. In general, GENERATE(<set1>, <set_expression>) will apply the set expression to each member of <set1> and join the results by union.

If <set1> is not related to <set_expression> by means of CURRENTMEMBER, GENERATE results in a simple replication of the set implied by <set_expression>, with as many replications as there are tuples in <set1>. If the optional ALL flag is specified, all duplicates are retained in the result. If ALL is not specified, duplicates are removed. For example, the statement

GENERATE({USA, FRANCE}, {SEATTLE, BOSTON}, [ALL])

will result in the following:

{SEATTLE, BOSTON, SEATTLE, BOSTON}

However, if ALL was not specified, the result is as follows:

{SEATTLE, BOSTON}

HIERARCHIZE(<set>)

Hierarchizes the set. Members in a level are sorted in their natural order, the default ordering of the members along a dimension when no other sort conditions are specified. HIERARCHIZE will always retain duplicates. The statement

HIERARCHIZE(SampleSet)

returns the set in natural order. The hierarchized dataset follows (assuming that the natural order for the data store is alphabetical).

INTERSECT(<set1>, <set2> [,[ALL]])

Finds the intersection of two input sets. By default, duplicates are eliminated from both sets prior to intersection. The optional ALL retains duplicates. Because there are several ways for ALL to work, it is worth mentioning the algorithm: Nonduplicated elements are intersected as usual. For each duplicate in <set1>, match it with a duplicate in <set2>, if one exists, and keep matching duplicates in the intersected set.

ORDER(<set>, {<string_value_expression> | <numeric_value_expression>} [, ASC | DESC | BASC | BDESC])

There are two varieties of ORDER: hierarchized (ASC or DESC) and nonhierarchized (BASC or BDESC, where "B" indicates "Break hierarchy"). The hierarchized ordering first arranges members according to their position in the hierarchy, and then it orders each level. The nonhierarchized ordering arranges members in the set without regard to the hierarchy. In the absence of an explicit specification, ASC is the default. The statement

ORDER(SampleSet, ([1995], Sales), DESC)

hierarchizes all members and sorts each level according to Sales. Sales are compared at the highest level when constructing the sorted list. Therefore, if the sum of Sales in all California cities is less than the sum of Sales in all New York cities, California and California. LA will appear below NYC in the sorted, descending list.

The result of

ORDER(SampleSet, ([1995], Sales), DESC)

is as shown in the following table.

The statement

ORDER(SampleSet, ([1995], Sales), BDESC)

sorts the members according to their values, without regard for their relative positions in the member hierarchy. In this example, numeric values are sorted by 1995 sales per city, including aggregate sales values by state and country.

The result of

ORDER(SampleSet, ([1995], Sales), BDESC)

is as shown in the following table.

TOPCOUNT(<set>, <index> [, <numeric_value_expression>])

Sorts on the <numeric_value_expression> (if any) and picks up the top <index> items. This function always breaks the hierarchy. The BOTTOMCOUNT function is similar.

For both these functions, the number of members returned is always equal to the specified <index>. Specifically, if there is a tie during the calculation of these members, the provider resolves the tie in a provider-dependent fashion and returns only the required number of members. For example, the statement

TOPCOUNT(Geography.Cities.MEMBERS, 5, Sales)

yields the following results, in terms of sales:

• The top four cities are New York, LA, Chicago, and Philadelphia.

• The fifth city is a tie between Seattle and Boston.

The provider can do either of the following:

1. Return all six members: {NYC, LA, Chicago, Philadelphia, Seattle, Boston}

2. Arbitrarily break the tie between Seattle and Boston and return only five cities: {NYC, LA, Chicago, Philadelphia, <either Seattle or Boston>}

OLE DB for OLAP requires that the provider not choose option 1 but instead, to resolve a tie in a provider-dependent manner, choose option 2 and return only the specified number of members.

TOPPERCENT(<set>, <percentage>, <numeric_value_expression>)

Sorts on <numeric_value_expression> and picks up the top x (the smallest number possible) elements such that their percentage of the result of <numeric_value_expression> is at least <percentage>. This function always breaks the hierarchy. The BOTTOMPERCENT function is similar.

TOPSUM(<set>, <value>, <numeric_value_expression>)

Sorts on <numeric_value_expression> and picks up the top x (the smallest number possible) elements such that their sum is at least <value>. This function always breaks the hierarchy. The BOTTOMSUM function is similar.

UNION(<set1>, <set2> [, [ALL]])

Joins the two input sets by union, eliminating duplicates by default. The ALL flag keeps duplicates in the joined set. Duplicates are deleted from the tail. The expression (<set1>, <set2>) does not remove duplicates.

Another way of joining by union is to use a comma-separated list of sets within braces. For example,

{USA.CHILDREN, CANADA.CHILDREN}

is an implicit union and is equivalent to

UNION(USA.CHILDREN, CANADA.CHILDREN, [ALL])

Duplicated members are always retained in an implicit union.