Set Value Expressions

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

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

with sales data as indicated in this 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.

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.

DESCENDANTS(USA, Counties, BEFORE_AND_AFTER)

yields all states and cities in USA.

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. 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.

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 follows.

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 follows.

Note   When the input set has 2 elements for which the string_value_expression or numeric_value_expression has the same value, then the input order is preserved. For example if the sales for USA & Europe is 300 each, and that for Asia is 100, then the call:

ORDER({USA, Europe, Asia}, Sales, BASC)

returns the set {Asia, USA, Europe}, and not the set {Asia, Europe, USA}.

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.

HIERARCHIZE(SampleSet)

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

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.

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.

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}

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.

DISTINCT(<set>)

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

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.

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 non-matching duplicates are retained.

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)}

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.

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

This is best explained with an example:

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

This 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, then 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, then all duplicates are retained in the result. If ALL is not specified, then duplicates are removed. For example,

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

will result in

{SEATTLE, BOSTON, SEATTLE, BOSTON}

However, if ALL was not specified, then the result is

{SEATTLE, BOSTON}