Basic Operators

This section lists the basic operators for manipulating strings, numbers, and logical conditions.

Value Operators

Table A-1 lists the basic operators. A ValueExpr can be either numeric or string, but it must be the same type on both sides of the operator. An ObjectExpr can refer to any metadata object or to NULL.

Expressions can be grouped with parentheses () to make the ordering of operations clear.

Table A.1: List of Operators

OPERATOR	RESULTS IN
NumericExpr + NumericExpr	Addition
NumericExpr − NumericExpr	Subtraction
NumericExpr ⊛ NumericExpr	Multiplication
NumericExpr / NumericExpr	Division
− NumericExpr	Unary negation
StringExpr + StringExpr	String concatenation (Microsoft extension)
ValueExpr < ValueExpr	Less than
ValueExpr > ValueExpr	Greater than
ValueExpr <= ValueExpr	Less than or equal to
ValueExpr >= ValueExpr	Greater than or equal to
ValueExpr <> ValueExpr	Not equal to
ValueExpr = ValueExpr	Equal to
BooleanExpr AND BooleanExpr	True if both expressions are true, false otherwise.
BooleanExpr OR BooleanExpr	True if either expression is true.
NOT BooleanExpr	True if expression is not true, false otherwise.
BooleanExpr XOR BooleanExpr	True if either of the expressions is true but not both of them, and false otherwise.
MemberExpr IS MemberExpr	True if the two member expressions evaluate to the same member. This is an extension to OLE DB for OLAP that both Microsoft and Hyperion implement. Note that NULL may be used in AS2000 and AS2005 to test if the other member reference is not valid.
ObjectExpr IS ObjectExpr	True if the two object expressions refer to the same object, and false otherwise. Any reference to a nonexistent object will return true when combined with NULL, as in Time.[All Time].Parent.Level IS Null. (Microsoft extension to OLE DB for OLAP).
SetExpr1 + SetExpr2	Set union, preserving duplicates as sequence (like { SetExpr1, SetExpr2 }; see below; Microsoft extension).
SetExpr1 − SetExpr2	Set difference (Microsoft extension).
-SetExpr	Complement of SetExpr (Microsoft extension). The SetExpr must consist only of members from one level of one hierarchy-dimension. Returns all members of that level except those appearing in SetExpr.
SetExpr1 ⊛ SetExpr1	Cartesian product of sets; see CrossJoin () (Microsoft extension).
Existing set_expression	Evaluates set_expression within the current member context. If the existence of the set's members or tuples depends on the current member context (in all dimensions other than those of the set_ expression), the returned set will reflect the context

Constructing Tuples

     ( member [, member . . .] ) 

Tuples can be explicitly constructed by listing members from one or more dimensions, enclosed within parentheses and separated by commas. If only one dimension is present in the tuple, the parentheses can be omitted as well. Any member specification will work, not just explicitly named members. For example, the following examples are all tuple specifications:

     [Time].[1997]     ([Time].[1997])     ([Time].[1997], [Customer].[All Customers])     ([Time].[1997], {[Customer].Members}.Item (0)) 

Note that when a function requires that a tuple be one of its arguments within parentheses, the parentheses must be placed around the tuple as well as used to enclose the argument list for the function. For example, the following would be a correct tuple specification to be passed to TupleToStr ():

     TupleToStr( ([Time].[1997], [Customer].[All Customers]) ) 

Trying to create an empty tuple with () will result in a syntax error. In Analysis Services 2005, you can create an essentially empty tuple with null member references. For example (null, null) specifies an empty tuple.

Constructing Sets

     { tuple or set [, tuple or set . . . ] } 

Sets can be explicitly constructed by enclosing one or more tuples or sets with the same dimensionality within curly braces, "{" and "}." Each tuple or set specification must be separated from the next by a comma. For example, the following are all sets:

     { [Time].[1997] }     { ([Time].[1997], [Customer].[All Customers]) }     { [Time].[All Time], [Time].[Year].Members,     { [Time].[Quarter].Members } } 

The first two are sets of one tuple each, and the last one is a set composed of one member and two sets, in order. Note that in the last example, one set is syntactically inside the other, and the inner one is also enclosed in curly braces. This is not required, and it does not affect the interpretation in any way. Although an empty set is not usually of much practical use, it may be created with an empty pair of curly braces: {}

     member : member 

This operator constructs a set from two members and uses the two members as endpoints. The two members must be on the same level; if they are not, a parse error will occur. If you use the database ordering of the members in the dimension, all members between the endpoints will be included. It is not an error for the two members to be the same (that is, {[Time].[2006] : [Time].[2006]}).

In Microsoft Analysis Services 2005, and 2000, if the member on the righthand side of the colon is earlier in the database ordering than the member on the left, a range is constructed from the member on the right to the member on the left (as though the members were flipped around the colon; the set will be in dimension order). In AS2005, if only one member is invalid and the other is valid, a range is constructed from the valid member to the end of the level. An invalid left-hand member creates a range from the first member in the level, and an invalid right-hand member creates a range to the last member in the level. In AS2000, if either of the members is invalid, a parse error occurs.

See also: MemberRange ()

Function and Operator Reference

A

AddCalculatedMembers (set) Returns: set Extension: AS2000, AS2005

By default, when a set of members is specified using a function that retrieves a set based on metadata (such as .Members, .Children, Descendants (), and so on), only base members are returned, even though calculated members may be within that range. The AddCalculatedMembers () function adds in all of the calculated members that are siblings of the members specified within the set. Each calculated member that was not already in the set is added in database order after its last sibling member in the set. The set is limited to only one dimension. Note that this function adds all calculated members defined, whether they were defined by CREATE MEMBER at the server or at the client, or in the query through WITH MEMBER.

See also: .AllMembers, StripCalculatedMembers ()

Aggregate (set[, numeric value expression]) Returns: number Standard

This function aggregates the cells formed by the set according to the default aggregation operator for any measures in context. If a numeric value expression is provided, then this function sums the expression's set of values over the cells. In the event that the cells are of a base measure, the aggregation function specified for the measure is used. In AS2000, if the aggregation functions of the measure are COUNT, MIN, or MAX, then COUNT, MIN, or MAX, respectively, is the aggregation operation used; otherwise, the aggregation operation used is summation. In AS2005, the list of intrinsic measure aggregation functions with which the Aggregate () function will work is much longer, including:

• Sum

• AverageOfChildren

• ByAccount

• Count

• FirstChild

• FirstNonEmpty

• LastChild

• LastNonEmpty

• Max

• Min

• DistinctCount

Aggregate () will not aggregate measures whose aggregation function is None, however.

Although you may specify an expression to be evaluated by this function, this function does not work if you use calculated members as its inputs. (If a calculated member "M" has a higher SOLVE_ORDER than a calculated member on a different dimension that is performing the Aggregate (), then "M" will use the results of the aggregating member.)

This comes in handy when you have a set of different measures with different aggregation rules that are all being queried for. Calculated members performing period-to-date aggregations as well as aggregations on other dimensions will often be best constructed out of this operator. (Essentially, this is the implicit operation carried out within Analysis Services's hierarchies.) Consider the following calculated member:

     CREATE MEMBER [Time].[MonthsOf2006ToDate] AS     'Aggregate ( {[Time].[Jan 2006] : [Time].[May 2006]} )' 

When combined with a summing measure, this member will yield the sum of its values over the range of January through May 2006. When combined with a measure aggregating by MAX, this member will yield the MAX of its values over that same time period.

In Analysis Services 2000, measures aggregated by DISTINCT COUNT cannot be aggregated by this function. This is fixed in Analysis Services 2005.

See also: Sum (), Count (), Min (), Max (), Avg (), DistinctCount ()

dimension.AllMembers

hierarchy.AllMembers Returns: set

level.AllMembers

All return: set

All are extensions: AS2000, AS2005

Generally, the .AllMembers functions are semantically equivalent to AddCalculatedMembers (Scope.Members), but they provide a more intuitive syntax. Although the Microsoft documentation only refers to .AllMembers for dimensions and levels, we note that in the case of multiple hierarchies on a dimension, you can only use this against one hierarchy.

The only case where .AllMembers and AddCalculatedMembers () differ is when no members are visible in the scope of [Dimension].Members, [Hierarchy].Members, or [Level].Members. This can occur on the measures dimension if all measures are hidden in a cube.

The following two statements will generally return the same set:

     [Measures].AllMembers     AddCalculatedMembers ([Measures].Members) 

See also: AddCalculatedMembers (), StripCalculatedMembers ()

Ancestor (member, level) Returns: member Standard

Ancestor (member, distance) Returns: member Extension: AS2005, AS2000

This function finds the source member's ancestor at the target level or distance. If the target level is the level of the source member, then the source member is returned. If a distance number is specified, it is the number of hierarchical steps above the member. A distance of 0 will return the source member. The behavior of Ancestor () is shown in Figure A-1.

Figure A-1: Behavior of Ancestor ().

See also: Descendants (), .Children

Ancestors (member, level) Returns: set Standard.

Ancestors (member, distance) Returns: set Standard.

The standard and Microsoft implementations of this function find the set of ancestors for the source member at the target level. If the database supports multiple parents for a single member in a hierarchy, then this function may return multiple members. Other databases (like Microsoft Analysis Services) will return a set of one member. If the target level is the level of the source member, then the source member is returned as a set.

The target level can be expressed either by name as a string expression or by distance as a numeric expression where 1 represents a parent, 2 represents a grandparent, and so on. Using 0 as a distance will return the member itself.

Note that although this function in theory may return multiple ancestors, the ancestors must be from the same hierarchy and the same level.

See also: Ancestor (), Ascendants (), Descendants (), .Children

expr1 AND expr2 Returns: Boolean Standard

The AND operator returns true if both expr1 and expr2 are true, and false otherwise. In Microsoft Analysis Services, if expr1 is false, then expr2 is not evaluated (there is no need to; the result is guaranteed to be false); this may be relevant when expr2 could have side effects or is costly to evaluate.

Ascendants (member) Returns: set Extension: AS2005, AS2000

This function returns the full set of ancestors for the given member all the way up to the root of the hierarchy or dimension. The ancestors are ordered from the bottom up, so that parents follow children. The given member is included in the set. It is very useful in queries when you want to include all higher level totals for a given member or set of members. The behavior of Ascendants () is shown in Figure A-2.

Figure A-2: Behavior of Ascendants ().

Note that the order of the resulting set must be changed by using Hierarchize () to get a top-down ordered set before being used in conjunction with any of the drill-related or VisualTotals () functions.

See also: Ancestor (), Ancestors (), Descendants (), .Children

Avg (set [, numeric expression]) Returns: number Standard

This function takes the average of the nonempty values found across cells related to the set. If a numeric expression is supplied, then its values are averaged across the cells in the set. Note that the average is formed out of the sum of the cells divided by the count of the nonempty cells. If you want to take the average over all cells, treating empty as zero, then you can either create a numeric value expression that converts missing to zero, or you can take the Sum () over the set divided by the Count () of the set, including empty cells.

See also: Aggregate (), Sum (), Count (), Min (), Max ()

Axis (Axis number) Returns: set Extension: AS2005, AS2000

This function returns the set of members or tuples that are included on a specified axis. Axis (0) returns the column tuples, Axis (1) returns the row members, and so on. This function is likely to be used most often in client applications when building queries. The following example uses Axis () in conjunction with Generate () and TopCount () to select the top two stores for each of the four quarters of 2006. Note that this statement will return results for all selected stores for each of the four quarters:

     SELECT     Generate (         Axis (1),         TopCount (             [Store].[Store Name].Members,             2,             ([Measures].[Amount],             Axis (1).Current)         ),         ALL     ) on 0,     {[Time].[2006].Children} on 1     FROM Sales 

You can use this set as the source for various set-related functions, such as .Count, Extract (), Distinct (), Except (), and so on. The .Item () function can be used as well. To use this function to select the top two stores based on the first member in the set down the rows, you would write the following:

     SELECT     TopCount (         [Store].[Store Name].Members,         2,         ([Measures].[Amount],         Axis (1).Item (0))     ) on 0,     {[Time].[2006].Children} on 1     FROM Sales 

Regardless of which dimensions were involved in the rows, the query would work fine (so long as [Store] is not in the rows!).

However, the execution of a query (building the axes and calculating cell values) does not provide a context on its own for iterating over the tuples in the set. For example, the following results in an error with the message stating that .Current cannot be used in this context:

     WITH     MEMBER [Measures].[Disp] AS     '[Measures].[Unit Sales]',     FORE_COLOR = 'iif (Axis (1).Current.Item (0).Level.Ordinal > 1, 0,     SELECT     { [Time].[Quarter].members } on 0,     { Ascendants ([Customers].[Name].&[2659]) } on 1     FROM Sales     WHERE  [Measures].[Disp]     CELL PROPERTIES FORMATTED_VALUE, FORE_COLOR 

This can be explained by the fact that the filter is always evaluated first. However, the following will also fail with the same error:

     WITH     MEMBER [Measures].[Disp] AS     '[Measures].[Unit Sales]',     FORE_COLOR = 'iif (Axis (1).Current.Item (0).Level.Ordinal .> 1, 0,     SELECT     { CrossJoin (         [Time].[Quarter].members,         { [Measures].[Disp] }     ) } on 0,     { Ascendants ([Customers].[Name].&[2659]) } on 1     FROM Sales     CELL PROPERTIES FORMATTED_VALUE, FORE_COLOR 

B

BottomCount (set, index [, numeric expression]) Returns: set Standard

See the description for TopCount ()

BottomPercent (set, percentage, numeric expression) Returns: set Standard

See the description for TopPercent ()

BottomSum (set, value, numeric expression) Returns: set Standard

See the description for TopSum ()

C

Calculate Returns: null Extension: AS2005

Aggregates the cell values within a cube according to the defined behavior. Before Calculate statement is applied contents of non-leaf cells are null.

CalculationCurrentPass () Returns: number (integer) Extension: AS2005, AS2000

This returns the current pass number for which the expression is being calculated. Typically, this will be used with iif () to pick out a particular expression based on the pass number. The lowest pass number is 0. Calculated cells begin calculations with pass 1.

See also: CalculationPassValue (), iif ()

CalculationPassValue (numeric expression, pass number [, flag ]) Returns: number

Extension: AS2005, AS2000

CalculationPassValue (string expression, pass number [, flag ]) Returns: string Extension: AS2005, AS2000

This function evaluates the given expression at the calculation pass identified by pass number and returns the value to the current calculation pass. If the flag is specified, it may be one of the following:

FLAG	DESCRIPTION
ABSOLUTE	The pass number is the absolute number of a pass (starting from zero). If you refer to a pass number that is higher than the currently executing pass, you will begin invoking the higher numbered pass for the cells that are referenced in the expression if they have not already executed that pass.
RELATIVE	This indicates to take the value from the pass that was pass number passes later. A negative pass number will refer to an earlier pass, and a positive pass number will invoke a later pass. A pass number that would refer to a pass earlier than pass 0 will silently cause a reference to pass 0.

Note that in AS2005, passes for session-scoped cell calculations (created by CREATE CELL CALCULATION) and those for query-scoped cell calculations (created by WITH CELL CALCULATION) are separated, so that query, session and global passes (from MDX scripts) cannot refer to calculations in each other.

See also: CalculationCurrentPass (), iif ()

Call UDF-Name ( [arguments] ) Returns: Void or rowset Extension: AS2005, AS2000

This function executes a registered external function that does not return anything (that is, a procedure in Visual Basic). Data can be passed to the function as if it were any other external function. In AS2005, Call can return either nothing or a rowset. In AS2000, the Call itself will return an empty cell value. Unlike other MDX functions and operators, this one cannot be combined with any other operators or functions; when used in an expression, the sole contents of the expression will be the Call invocation. Here's an example:

     Call MailMsgToUser (         [Employee].CurrentMember.Properties ("Email Address"),         "Look at department" + [Department].CurrentMember.Name     ) 

CASE

     CASE reference-expression WHEN test1 THEN result1     [ ... WHEN testN THEN resultN ]     [ ELSE DefaultResult ]     END 

Returns number, string, member, tuple, set, level, hierarchy, array (all AS2005 only)

Standard (except AS2000)

     CASE WHEN textExpr1 THEN result1     [ ... WHEN testExprN THEN resultN ]     [ ELSE DefaultResult ]     END 

Returns number, string, member, tuple, set, level, hierarchy, array (all AS2005 only)

Standard (except AS2000)

This operator provides a multiway conditional test for values to return. It is similar to the iif () function, but can handle more conditions. The different implementations provide different capabilities.

The expression in the first form can be either a numeric expression or a string expression, and each test must the same type (numeric or string). Each test from test1 through testN is evaluated in sequence until the result of a test is equal to the reference-expression. When that happens, the corresponding result following the THEN clause is returned. If no test expression is equal to reference expression, then the DefaultResult, if any, is returned. If none is specified, then the result of the function is NULL.

In the second form of CASE, each testExpr is evaluated as a Boolean expression in sequence until one evaluates as true. The result expression of the corresponding THEN clause is returned. If no testExpr evaluates to true, then the DefaultResult, if any, is returned. If none is specified, then the result of the function is NULL.

In AS2005, the type of result from any THEN clause can be different from that of any other THEN clause. A single CASE operator could return strings, numbers, and/or any of the other types listed depending on the reference-expression and/or test expressions. When no test evaluates to true and there is no default clause, a NULL is returned.

AS2005 allows the CASE expression to serve as an axis expression or as the input to other functions that use sets, such as Avg () or Generate (). For AS2005, since CASE can return most MDX objects, you can write expressions such as the following, which uses the CASE construct to pick an attribute dimension whose members end up in a named set [ASet]:

     WITH SET [ASet] AS     CASE     WHEN condition1     THEN [Customer].[Region]     WHEN condition2     THEN [Customer].[State]     ELSE [Customer].[City]     END .Members     ... 

See also: CoalesceEmpty (), iif ()

member.Children Returns: set Standard

This function returns the children of the given member. member.Children is equivalent to {member.FirstChild : member.LastChild}. As you might expect, if you apply member.Children to a leaf member, the result is no members (an empty set). Figure A-3 illustrates the behavior of the .Children function.

Figure A-3: member.Children.

See also: Ancestor (), Descendants (),.Parent, .Siblings

ClosingPeriod ([level [, member]]) Returns: member Standard

See description of OpeningPeriod ()

CoalesceEmpty (value expression [, value expression … ]) Returns: number or string

Standard

This function evaluates the first value expression listed. If it is not NULL, then the value of that expression is returned. If it is NULL, then the second value expression is evaluated and it is returned if it is not NULL. Each subsequent expression, if present, is evaluated in turn; if the last one is NULL, the entire operator returns NULL.

CoalesceEmpty () can either take all number-valued expressions and return a number or it can take all string-valued expressions and return a string.

See also: iif (), IsEmpty (), CASE

Correlation (set, y numeric value expression [, x numeric value expression])

Standard

This function calculates a correlation coefficient between x-y pairs of values. The y numeric expression is evaluated over the set to get the y values for each pair. If the x numeric expression is present, then it is evaluated over the set. Otherwise, the cells formed by the set are evaluated within the current context, and their values are used as the x values. The formula for the correlation coefficient is as follows:

If either the y or the x numeric expression is a logical or text value, or if the value is NULL, then that tuple and its related values are not included in the correlation. Zero values for y and x are included.

Count (set [, INCLUDEEMPTY | EXCLUDEEMPTY]) Returns: number (integer)

Extension: AS2005, AS2000

This function counts the cells in the range formed by the set (as opposed to counting the tuples in the set). In Analysis Services, without the INCLUDEEMPTY flag, only nonempty cells are counted; with the flag, all cells are counted. INCLUDEEMPTY is the default.

See also: .Count, Sum (), Avg (), DistinctCount (), NonEmptyCount ()

Dimension.Count Returns: number (integer) Extension: AS2005, AS2000

This function counts the number of dimensions in a cube. For AS2005 it returns the total number of hierarchies within the cube.

Levels.Count Returns: number (integer) Extension: AS2005, AS2000

This function count returns the number of levels in a dimension on hierarchy including [All] level.

Set.Count Returns: number (integer) Extension: AS2005, AS2000

This function counts the tuples present in Set. It is equivalent to Count (Set, INCLUDEEMPTY) but is syntactically simpler.

See also: Rank (), Count (), Avg (), Set .Item ()

Tuple.Count Returns: number (integer) Extension: AS2005, AS2000

This function counts the dimensions present in Tuple.

See also: Tuple .Item ()

Cousin (member, ancestor_member) Returns: member Standard

This function returns the member that has the same relative position under a specified ancestor member as the initial member specified. The Cousin () function is best understood by walking through its algorithm. Figure A-4 shows the behavior of the Cousin () function. From the member's level to the ancestor_member's level, Cousin () tracks which sibling it is related to under its ancestor at that level. [March 2001] is the third child of the first child of [2001]. The same path is then followed from the ancestor member down to the level of member. [March 2002] is the third child of the first child of [2002]. Because of the straightforwardness of this algorithm, it works best when you can guarantee the same number of descendants under each ancestor. For example, it is likely that years, quarters, and months or days, hours, and minutes can be used with Cousin (), because each of these levels has a fixed relationship within itself. However, a cousin of January 31 in February will not exist because February will not have a thirty-first day.

Figure A-4: Cousin () function.

See also: ParallelPeriod (), PeriodsToDate ()

Covariance (set, y numeric expression [, x numeric expression]) Returns: number

Standard ( 9)

CovarianceN (set, y numeric expression [, x numeric expression]) Returns: number

Standard

Covariance () calculates the population covariance and uses the biased population formula (dividing by the number of x-y pairs). CovarianceN () calculates the sample covariance and uses the unbiased population formula (dividing by the number of x-y pairs minus 1). If either the y or the x numeric value expression is a logical or text value, or if the value is NULL, then that tuple and its related values are not included in the correlation. Zero values for y and x are included.

These functions calculate the statistical covariance across x-y pairs of values. The y numeric expression is evaluated over the set to get the y values for each pair. If the x numeric expression is present, then it is evaluated over the set. Otherwise, the cells formed by the set are evaluated within the current context, and their values are used as the x values. The biased population formula for covariance is as follows:

CrossJoin (set1, set2) Returns: set Standard (additional behavior in AS2005)

set1 ⊛ set2 Returns: set Extension: AS2005, AS2000

These functions return a set forming the Cartesian product of the two sets (except for a Microsoft extension to the semantics noted below). The two sets must represent different dimensions; you will get an error if the same dimension appears in both of them. CrossJoin () only takes two sets as arguments. However, because it takes two sets as input and returns a set as its output, you may nest multiple calls to CrossJoin () to take the Cartesian product of three or more dimensions. Following the same rules used for composing tuples by hand, the order of the dimensions in the resulting tuples is the same as the order of dimensions in the set arguments. Using an asterisk between two sets, as with {set1 ⊛ set2}, is a Microsoft-specific synonym for CrossJoin (). The expression set1 ⊛ set2 ⊛ set3 is the same as CrossJoin (set1, CrossJoin (set2, set3)).

In Analysis Services 2005, if the two sets are composed of tuples from the same base dimension but different attribute hierarchy-dimensions, then only the combinations of tuples that actually exist in the underlying dimension are returned. There is no way to produce tuples that do not have corresponding entries in underlying tables.

See also: Extract (), Generate (), Distinct (), NonEmptyCrossJoin ()

Set.Current Returns: tuple Standard

This function returns the current tuple from a set within an iteration over the set. set.Current is only valid while there actually is an iteration occurring over the set. It returns a full tuple from the set. The set needs to be named, and can be either a set alias or a named set.

See also: .CurrentMember, .CurrentTuple, Generate ()

dimension[.CurrentMember] Returns: member Standard

This function returns the current member in that dimension. "Current" is relative to the context that the calculation is taking place in. That context may be the axis of a query being executed or a Generate () function within that query. We indicate that .CurrentMember is optional. The default operator applied to a dimension is .CurrentMember.

Note that the MDX specification states that .CurrentMember may be applied to any set to return the current tuple. Analysis Services restricts the application of this operator to a set that has a single dimension, which will then return a single member. The .Current operator is applied to an arbitrary set to retrieve a tuple.

In Analysis Service 2005, keep in mind that the current member of an attribute dimension is influenced by the "current" members of other related attribute dimensions and the underlying base dimension.

See also: .Current, .CurrentOrdinal

set.CurrentOrdinal Returns: number Extension: AS2005

This function returns the current iteration number within a context that iterates over set. The Filter () and Generate () functions provide an appropriate iteration context. Functions like Sum () and Order () do not. The set must be an alias name — it cannot be a named set.

See also:. Current, .Current Tuple, .CurrentMember

CustomData Returns: string Extension: AS2005

This function returns the current value of the CustomData connection property. If this property was not set, then the function returns NULL. This can be used to pass in one arbitrary configuration setting to be used by MDX functions.

See also: UserName, Call ()

D

Member.DataMember Returns: member Extension: AS2005, AS2000

This function returns the system-generated data input member associated with a member (as opposed to the input data). In AS2000 this function generally applies to parent-child dimensions where data is input at the parent level and also calculated by aggregating the parent's children. In AS2005, you can use this on any hierarchy. The following example produces both the input individual salary and the aggregated organizational salary for each employee:

     WITH MEMBER [Measures].[Individual Salary] AS '([Employees].CurrentMember.DataMember, [Measures].[Salary])' SELECT { [Employees].Members } on columns, { [Measures].[Salary], [Measures].[Individual Salary] } on rows     FROM HRCube 

Note that when using the UPDATE CUBE command, the .DataMember function enables you to write data to the actual member, as opposed to the member's leaf descendants.

dimension.DefaultMember Standard

hierarchy.DefaultMember Standard

Each of these returns the default member for the dimension or hierarchy. If the dimension has an All level and member, then the default member is the All member. If the dimension does not have an All member, then an arbitrary member from its top level will be the default member. Microsoft Analysis Services also allows you to override these defaults at the server or through their ALTER CUBE UPDATE DIMENSION command.

See also: .CurrentMember

Descendants (member, [level [, desc_flag ]])

Standard

Descendants (member, distance [, desc_flag ])

Standard

This function returns a set of descendants of the given member using the indicated level, or numeric distance from the specified member's level, as a reference point. The desc_flag parameter is used to pick from the many possible sets of descendants. If no level or desc_flag is provided, then the member and all of its descendants are returned. Figures A-5 through A-12 illustrate the behavior of the Descendants () operator. The flags are:

• SELF

• AFTER

• BEFORE

• SELF_AND_AFTER

• SELF_AND_BEFORE

• SELF_BEFORE_AFTER

• BEFORE_AND_AFTER LEAVES

Figure A-5: Behavior of descendants () with SELF FLAG.

Figure A-6: Behavior of descendants () with AFTER flag.

Figure A-7: Behavior of descendants () with BEFORE flag.

Figure A-8: Behavior of descendants () with SELF_AND_AFTER flag.

Figure A-9: Behavior of descendants () with SELF_AND_BEFORE flag.

Figure A-10: Behavior of descendants () with BEFORE_AND_AFTER flags.

Figure A-11: Behavior of descendants () with SELF_BEFORE_AFTER flags.

Figure A-12: Behavior of Descendants () with LEAVES flag.

SELF refers to the level listed as the second argument and means to take the members at that level. AFTER refers to the level or levels that appear below the level listed as the second argument. BEFORE refers to the level or levels that appear above the level listed and below the member given as the first argument. The BEFORE_AND_AFTER, SELF_AND_ AFTER, SELF_AND_BEFORE, and SELF_BEFORE_AFTER flags combine these basic options, as shown in Figures A-5 through A-12.

The LEAVES flag is used in conjunction with a depth number and is intended for use with ragged and parent-child hierarchies. If a depth number is specified without LEAVES, then only members that are at that depth are returned. If a depth number is specified with LEAVES, then any leaf members encountered up to that depth are retained. In Analysis Services, you can request leaf-level members regardless of their depth by leaving the depth argument empty. The following would perform that:

     Descendants (         [Accounts].CurrentMember,         , /* empty */         LEAVES     ) 

In Essbase, you don't need to use LEAVES, since you can request leaf-level members by referring to the dimension's level 0 (remember that in Essbase, levels refer to heights while generations refer to depths). The following would retrieve leaf-level members in Essbase:

     Descendants (         [Accounts].CurrentMember,         [Accounts].Levels (0)         /* LEAVES is optional at this point */     ) 

If no flag is specified, the default behavior is SELF.

See also: Ancestor (), Ancestors (), Ascendants (), .Children

Hierarchy.Dimension Returns: dimension Extension: AS2005, AS2000

This function returns the dimension that the hierarchy is in. Because Microsoft Analysis Services 2000 and, to some degree, 2005 semantically treat different hierarchies as different dimensions, this function is essentially a "no-op" in those products.

Level.Dimension Returns: dimension Extension: AS2005, AS2000

The function returns the dimension that contains Level.

Member.Dimension Returns: dimension Extension: AS2005, AS2000

This function returns the dimension that contains Member.

Dimensions (numeric expression) Returns: dimension Extension: AS2005, AS2000

This function returns the dimension whose zero-based position within the cube is numeric expression. Note that the Measures dimension is always Dimensions (0), while the order of the other dimensions depends on the order in which they were added to the cube when it was being constructed (and/or modified).

Dimensions (string expression) Returns: dimension Extension: AS2005, AS2000

This function returns the dimension whose name is given by string expression.

See also: Dimension .Name

Distinct (set) Returns: set Standard

This function removes any duplicates from the set. The first instance of each tuple is retained in the order in which it appears.

See also: DistinctCount (), Except (), Extract ()

DistinctCount (set) Returns: number (integer) Extension: AS2005, AS2000

This function counts the distinct, nonempty tuples in a set. It is equivalent to Count (Distinct (set),EXCLUDEEMPTY). Only a measure can use this function. If you define a calculation on another dimension that uses DistinctCount (), you will get a syntax error. (If you want this functionality on another dimension, you can use the equivalent Count (…) expression.)

When this function is used to calculate a cell, the distinct tuples in set are determined, and the nonempty cells formed by intersecting those tuples with the current member in every other dimension are counted. This function can be used to simulate the DistinctCount measure aggregation type in Analysis Services, but its strength is when you want the distinct count along only a subset of cube dimensions (one or two), when you are limiting the scope within the dimensions, when you are taking the distinct count at aggregate members in one or more of the dimensions, or when one or more dimensions involve a calculated member. Remember that the DistinctCount aggregation is handled to some degree by the server during cube aggregation, while this function is calculated at client query time.

See also: Distinct (), Count (), .Count

DrillDownLevel (set [, level]) Returns: set Standard

This function returns a set resulting from a particular drill-down operation performed by the function. Set can be of arbitrary dimensionality. When the level argument is specified, all members or tuples in set that are in level are drilled down into the next lowest level (if there is one). When the level argument is not specified, only those members or tuples that are at the lowest level in the first dimension of the set are drilled down into, and they are drilled down into the next lower level. The behavior of DrillDownLevel () is shown in Figure A-13. All children are inserted immediately after their parents; otherwise, the order is preserved. If level is specified, but there is no member at level in the set, then the given set is returned without modification.

Figure A-13: DrillDownLevel ().

In Essbase, the layer can be a generation or level specification.

If one or more children of a member to be drilled down into immediately follows a parent in set, then that parent will not be drilled down into.

DrillDownLevel (set, index) Returns: set Extension: AS2005, AS2000

This variation is a Microsoft - specific extension to DrillDownLevel (). It enables the dimension to be drilled down into by leaving the level field empty and providing a zero-based dimension index to specify which dimension should be drilled down into. This is really only useful when set has tuples with more than one dimension. The first dimension to drill down into is at index 0, the second dimension is at index 1, and so on. As with the rules for the standard version of DrillDownLevel (), tuples containing the lowest level members of that dimension are drilled down into.

If one or more children of a member to be drilled down into immediately follows a parent in set, then that parent will not be drilled down into.

DrillDownLevelBottom (set, index [,[level] [, numeric expression]]) Returns: set

Standard

Similarly to DrillDownLevel () and DrillDownLevelTop (), this function drills down through all members in the set that are at the specified level, if the level is provided (or the lowest level of members that are present in the set if level is not provided). However, instead of returning all children, this function returns only the bottom index members or tuples. The set can be of arbitrary dimensionality. The ranking is determined through the numeric expression, if one is provided, or through the values of cells found in the default context when the set is evaluated, if the numeric expression is left out. Figure A-14 illustrates the behavior of DrillDownLevelBottom ().

Figure A-14: DrillDownLevelBottom ().

If one or more children of a member to be drilled down on immediately follows a parent in the set, then that parent will not be drilled down into.

DrillDownLevelTop (set, index [, [level] [, numeric expression]] ) Returns: set Standard

Similarly to DrillDownLevel () and DrillDownLevelBottom (), this function drills down all members in set that are at the specified level, if the level is provided (or the lowest level of members that are present in the set if level is not provided). However, instead of returning all children, this function returns only the top index members or tuples. The set can be of arbitrary dimensionality. The ranking is determined through the numeric expression, if one is provided, or through the values of cells found in the default context when the set is evaluated, if the numeric value expression is left out. Figure A-15 illustrates the behavior of DrillDownLevelTop (). As with DrillDownLevel (), if a member at level is immediately followed by one of its children, it will not be drilled down on.

Figure A-15: DrillDownLevelTop ().

If one or more children of a member to be drilled down into immediately follows a parent in set, then that parent will not be drilled down into.

DrillDownMember (set1, set2 [, RECURSIVE]) Returns: set Standard

This function returns a set that is formed by drilling down one level on each member in set1 that is present in set2. Set1 can be of arbitrary dimensionality; set2 must be of only one dimension. The ability of set1 to consist of more than one dimension is an extension to the OLE DB for OLAP specification.

If set1 contains tuples, this function will return a set that is formed by drilling down each tuple in set1 that has a matching member from set2 in it. If RECURSIVE is not specified, then only one pass through set1 is performed, matching each member or tuple with each member in set2. If RECURSIVE is specified, then the set resulting from the first pass is again matched with each member in set2, and so on until no more members in the set being constructed are found in set2. Figure A-16 illustrates the behavior of DrillDownMember ().

Figure A-16: DrillDownMember ().

If one or more children of a member to be drilled down into immediately follows a parent in set, then that parent will not be drilled down into.

See also: DrillUpMember ()

DrillDownMemberBottom (set1, set2, index [, numeric expression][, RECURSIVE]) Returns: set

Standard

Much like DrillDownMember (), this function returns a set that is formed by drilling down one level on each member in set1 that is present in set2. However, it returns the bottom index children for a parent rather than all children. Set1 can be of arbitrary dimensionality; set2 must be of only one dimension.

If set1 contains tuples, this will return a set that is formed by drilling down each tuple in set1 that has a matching member from set2 in it. If RECURSIVE is not specified, then only one pass through set1 is performed, matching each member or tuple with each member in set2. If RECURSIVE is specified, then the set that results from the first pass is again matched with each member in set2, and so on until no more members in the set being constructed are found in set2. At each step of drilling, the bottom index child members or tuples are returned instead of all children. The ranking is based on the numeric expression, if specified; otherwise, values from the set of children are evaluated in the current context, and those results are used. Figure A-17 illustrates the behavior of DrillDownMemberBottom ().

Figure A-17: DrillDownMemberBottom ().

DrillDownMemberTop (set1, set2, index [, numeric expression][, RECURSIVE]]) Returns: set

Standard

Like DrillDownMember (), this function returns a set that is formed by drilling down one level on each member in set1 that is present in set2. However, it returns the top index children for a parent rather than all children. Set1 can be of arbitrary dimensionality; set2 must be of only one dimension.

If set1 contains tuples, this will return a set formed by drilling down each tuple in set1 that has a matching member from set2 in it. If RECURSIVE is not specified, then only one pass through set1 is performed, matching each member or tuple with each member in set2. If RECURSIVE is specified, then the set that results from the first pass is again matched with each member in set2, and so on until no more members in the set being constructed are found in set2. At each step of drilling, the top index child members or tuples are returned instead of all children. The ranking is based on the numeric expression, if specified; otherwise, values from the set of children are evaluated in the current context, and those results are used. Figure A-18 illustrates the behavior of DrillDownMemberTop ().

Figure A-18: DrillDownMemberTop ().

DrillUpLevel (set [, level]) Returns: set Standard

This function strips away all members in the set that are below the given level. (In Essbase, either a generation or level may be used.) If the level is not provided, then it is assumed to be one level higher in the hierarchy than the level of the lowest level member(s) in the set (the lowest-level members in the set are removed). Figure A-19 illustrates the behavior of DrillUpLevel (). A set returned by DrillDownMember () or DrillDownLevel () will be suitable for cleanly drilling up with this function.

Figure A-19: DrillUpLevel ().

See also: DrillDownLevel (),DrillDownByLayer (),DrillUpByLayer (),

DrillUpMember (set1, set2 ) Returns: set Standard

This step strips away members in set1 that are descendants of members in set2. Figure A-20 illustrates the behavior of DrillUpMember (). Set1 can contain tuples of arbitrary dimensionality; set2 must contain only members of one dimension.

Figure A-20: DrillUpMember ().

Note that only descendants that are immediately after the ancestor member in set2 are stripped away. If an ancestor member specified in set2 is not present in set1, any descendants will remain. Descendants that precede the ancestor or that appear after another member that is not a descendant has intervened will not be stripped away. A set returned by DrillDownMember () or DrillDownLevel () will be suitable for drilling up cleanly with this function. Figure A-20 illustrates the behavior of DrillUpMember ().

See also: DrillDownMember ()

E

Error ( [ string_expr ] ) Returns: (no return) Extension: AS2005

The Error () function raises an error. This will propagate to callers; if an error occurs for evaluating a cell that is an input to a function like Filter () or Order () in an axis or slicer, the query will not successfully execute. If the error occurs while calculating a result cell, then the client will receive an error result when retrieving that cell's value. It may be possible to detect and work around the error with the IsError () function that is part of VBA and .Net, although this may not work correctly until service pack 1 of AS 2005.

Except (set1, set2 [, ALL]) Returns: set Standard

set1 - set2 Returns: set Extension: AS2005, AS2000

The Except () function removes all elements from set1 that also exist in set2. The ALL flag controls whether duplicates are retained or eliminated. When ALL is specified, duplicates in set1 are retained, though any tuples matching them in set2 are discarded. When ALL is not specified, no duplicates are returned. The members returned are determined by the order in which they appear in set1.

See also: Union (), Intersect (),and the unary - (complement) operator for sets

Microsoft Analysis Services also provides "−" as an alternate way of specifying Except (). Duplicates are removed from the resulting set. The expression Set1– Set2 is equivalent to Except (Set1, Set2).

See also: Union (), Intersect (), and the unary - (complement) operator for sets

Existingset Returns: set Extension: AS2005

This function applies the attribute relationships in effect in the current context to restrict the tuples in set. For example, if the [Product].[Ship Weight] current context consists of the member [12], then Existing [Product].[SKU].Members will return only those [SKU] members associated with the ship weight [12]. The current context may contain multiple members for each related attribute (for example, due to a set in the slicer or in a defined subcube), in which case the set will be restricted to tuples which are associated with at least one of the members in context.

See also: Exists

Exists (set1, set2 [, measure_group_name]) Returns: set Extension: AS2005

Returns all tuples in set1 which exist with respect to the tuples in set2. Set1 and set2 may or may not include related attribute hierarchy-dimensions. When the optional measure_group_name is provided, it uses existence of fact records for the measure group as the basis for relating members. members when set1 and set2 contain tuples from different dimensions (not just different hierarchy-dimensions). In this case, it is similar to NonEmpty (). If all measures in the measure group are NULL in a fact table row, Exists () will consider the relationship to exist, whereas if a fact table row doesn't exist at all, it will consider the relationship to not exist. This is different from NonEmpty (), which requires at least one non- NULL measure value to exist for non-emptiness to be established.

See also: CrossJoin (), Extract (), NonEmpty ()

Extract (set, dimension[, dimension … ]) Returns: set Standard

This function behaves as an opposite to the CrossJoin () function. The resulting set consists of tuples from the extracted dimension elements. For each tuple in the given set, the members of the dimensions listed in the arguments are extracted into new tuples. Since this could result in a great deal of redundancy, this function always removes duplicates from its results.

See also: CrossJoin (), Generate ()

F

Filter (set, search condition) Returns: set Standard

Filter returns those tuples of set for which the search condition (a logical expression) is true. If none are true, an empty set is returned. The tuples in the resulting set follow the same order in which they appeared in the original set. Note that the search condition must be phrased as a Boolean expression; you cannot use the assumption that a nonzero numerical result means "true" and a zero numerical result means "false."

See also: iif (), CoalesceEmpty ()

member.FirstChild Returns: member Standard

member.LastChild Returns: member Standard

These functions return the first child or last child of the member according to the database ordering of the child members. Their behavior is illustrated in Figure A-21.

Figure A-21: .FirstChild and .LastChild.

See also: .FirstSibling, .LastSibling, .Children, .Siblings

member.FirstSibling Returns: member Standard

member.LastSibling Returns: member Standard

Figure A-22 shows the behavior of the .FirstSibling and .LastSibling operators. The first child of a parent is its own first sibling, and the last child is its own last sibling. If no parent exists, then the first member in that level is the first sibling and the last member in the level is the last sibling. For example, the All or root member of a dimension is its own first and last sibling. In a dimension without an All level, the first member of the top level is the first sibling of all members at that level, and the last member of the top level is the last sibling of all members at that level.

Figure A-22: .FirstSibling and .LastSibling.

See also: .Siblings, .FirstChild, .LastChild

G

Generate (set1, set2 [, ALL]) Returns: set Standard

Generate (set, string expression, [delimiter]) Returns: string Extension: AS2005, AS2000

The set version of Generate () iterates over each tuple in set1, and for each element in set1, it puts every element specified by set2 into the result set. The dimensionality of the result set is the dimensionality of set2. If ALL is specified, then duplicate result tuples are retained. If ALL is not specified, duplicates after the first are removed. Set1 and set2 may be composed of completely different dimensionality, or they may be composed of exactly the same dimensionality. When set2 is a relatively static set of members, this function behaves much like CrossJoin (). Generate () gains its iterative power when set2 is an expression that depends on the current member or tuple in set1.

The string version of this function iterates over each tuple in the set specified as the first argument, evaluates the string expression for the current member of the set, and returns the concatenated result, optionally with a delimiter. For example, the following generates an HTML table of member names:

     "<table><tr><td>" + Generate (        [Product].[ByCategory].CurrentMember.Children,    [Product].[ByCategory].CurrentMember.Name,    "</tr></td><tr><td>"   ) + "</tr></td></table>" 

See also: CrossJoin (), Extract ()

H

Head (Set [, Count]) Returns: set Extension: AS2005, AS2000

This function returns a set of the first Count elements from the given set. The order of elements in the given set is preserved. If Count is omitted, the number of elements returned is 1. If Count is less than 1, an empty set is returned. If the value of the Count is greater than the number of tuples in the set, the original set is returned.

See also: Tail (), Subset (), Rank (), .Item ()

Hierarchize (set) Returns: set Standard

Hierarchize (set, POST) Returns: set Extension: AS2005, AS2000

Hierarchize () returns the set that it is given after it puts all the members in each dimension of set into hierarchical order. By default, within each level, members are put into their database ordering, top down. Children are sorted to immediately follow after their parents. The optional POST keyword returns the members in bottom-up rather than top-down order; that is, the children precede their parents. When the tuples are composed of more than one dimension, they are sorted primarily on the first dimension, then on the second dimension, and so on. Any duplicate tuples are retained.

In Analysis Services, Hierarchize () is similar to sorting on the members' internal ID property.

See also: Order (), Ascendants (), Ancestor (), .Parent

level.Hierarchy Returns: hierarchy Standard

This function returns the hierarchy that contains the level. Because Analysis Services semantically treats different hierarchies as different dimensions, this function is essentially equivalent to Level.Dimension.

member.Hierarchy Returns: hierarchy Standard

This function returns the hierarchy that contains the member. Because Analysis Services semantically treats different hierarchies as different dimensions, this function is essentially equivalent to Member.Dimension.

I

dimension.Ignore Returns: member Extension: AS2000

When used in an expression, .Ignore fixes the member of dimension at the current one in the context and prevents any further recursion along that dimension. In recursive calculations, sometimes a cell reference will end up being circular. For example, the level-wide custom rollup expression

     IIf (IsLeaf ([Accounts].CurrentMember),       [Accounts].CurrentMember,       RollupChildren ( [Accounts].CurrentMember,          Accounts.CurrentMember.Properties ("UNARY_OPERATOR") )     ) 

becomes recursive at leaf levels, because the evaluation of the [Accounts].CurrentMember at the leaf level will still result in another cycle through the whole iif () clause. Modifying the expression to

     IIf (IsLeaf ([Accounts].CurrentMember),       [Accounts].Ignore,       RollupChildren ( [Accounts].CurrentMember,          Accounts.CurrentMember.Properties ("UNARY_OPERATOR") )     ) 

fixes the problem; no more recursion will take place on the Account dimension.

Iif (search_condition, true_part, false_part) Returns: number, string, member, tuple, level, hierarchy, array (all only in AS2005)

The standard version of the iif () function can either take numerical expressions for the true part and the false part and return a number, or it can take string expressions for the true part and the false part and return a string. Analysis Services 2005 extends this to allow the parts to have separate types, and for the results to be almost any MDX object (numeric and string values, members, sets, tuples, levels, hierarchies, dimensions and arrays). be numeric or string separately from each other.

AS2005 allows you to return sets an hence, you can write expressions like the following:

     SELECT IIF ( condition, set1, set2) on axis (0) ...     Avg (       iif ( condition, LastPeriods (3), LastPeriods (4)), [Measures].[Units]       [Measures].[Units]     ) 

AS2005 allows iif () to return other kinds of things as well. For example, you can select the members from a level determined dynamically with the following:

     SELECT     IIF (condition,       [Customer].[Customer].[Region],       [Customer].[Customer].[State]     ).Members on axis (1) ... 

This function evaluates search_condition, which can be any value expression in AS 2005 and any logical or numeric expression in AS 2000. If the result is true, or at least nonzero in Analysis Services, then the true_part expression is evaluated and returned. If the result is not true, then the false_part expression is evaluated and returned. The standard version of the iif () function can either take numerical expressions for the true part and the false part and return a number, or it can take string expressions for the true part and the false part and return a string. Microsoft extends this to allow the parts to be numeric or string separately from each other.

Note that when the search condition contains a logical expression that involves comparison operations, since NULL cells compare as equal to zero with any comparison operator, the result of the search condition cannot be NULL. However, either the true_part or the false_part may evaluate to NULL, in which case NULL will be the result when that condition is met.

See also: CoalesceEmpty (), Filter (), CASE

Intersect ( set1 , set2 [,ALL]) Returns: Standard

The ALL flag controls whether duplicates are retained or eliminated. When ALL is not specified, only the unique tuples appearing in set1 that also appear in set2 are returned. When ALL is specified, then duplicated tuples in set1 that appear anywhere in set2 are returned. If duplicates of a tuple occur in set2, only the duplicates that exist in set1 will end up in the resulting set. The members are returned in the order in which they appear in set1. For example, the expression

     Intersect (       {[Customer].[AZ].[Phoenix], [Customer].[AZ].[Scottsdale],         [Customer].[KS].[Pittsburg], [Customer].[AZ].[Phoenix]},       {[Customer].[NM].[Albuquerque], [Customer].[AZ].[Phoenix],         [Customer].[AZ].[Scottsdale], [Customer].[AZ].[Phoenix]},     ) 

yields the following set:

         { [Customer].[AZ].[Phoenix], [Customer].[AZ].[Scottsdale] } 

The expression

     Intersect (       {[Customer].[AZ].[Phoenix], [Customer].[AZ].[Scottsdale],          [Customer].[KS].[Pittsburg], [Customer].[AZ].[Phoenix]},       {[Customer].[NM].[Albuquerque], [Customer].[AZ].[Phoenix],          [Customer].[AZ].[Scottsdale], [Customer].[AZ].[Phoenix]},          , ALL     ) 

yields the following set:

     { [Customer].[AZ].[Phoenix], [Customer].[AZ].[Scottsdale],           [Customer].[AZ].[Phoenix]} 

object1 IS object2 Returns: boolean Extension AS2005, AS2000

The Is operator is used to determine if two objects are equivalent. For example, the expression

     [Customers].CurrentMember     IS [All Customers].[Canada].[BC].[Vancouver] 

will only return TRUE when the current customer member in the context is Vancouver, BC. In Analysis Services, you can compare objects of any kind, and you can compare objects with NULL as well to see if they exist. For example, if the first month in the Time dimension is [Jan 2000], then the following two expressions will return TRUE:

     [Jan 2000].PrevMember IS NULL         [Jan 2000].Level IS [Time].[Month] 

See also: IsEmpty (), IsValid ()

IsAncestor (AncestorMember, StartingMember) Returns: boolean Extension: AS2005, AS2000

This function returns true if the AncestorMember is indeed a proper ancestor of StartingMember, and false otherwise. No error is returned if the two members are from different dimensions (just false).

See also: IsChild (), IsGeneration, IsSibling (), IsLeaf (), iif (), Is, .Ordinal

IsEmpty (ValueExpression) Returns: boolean

Standard

This function returns true if the ValueExpression is NULL, and false otherwise. Note that in Analysis Services, if the ValueExpression is a tuple instead of a simple member reference, then it must be enclosed by parentheses to distinguish the use of parentheses for tuple construction from parentheses for delimiting the argument to IsEmpty (), as in:

         IsEmpty ( ([Measures].[Units], [Time].PrevMember) ) 

Also, in Analysis Services, note that IsEmpty () will return false if a property reference is not valid for the member (like IsEmpty ([Time].CurrentMember.Properties ("Mailing Address")). More generally, in AS 2005, it will return false if evaluation of ValueExpression raises an error.

See also: iif (), IS, IsValid (), Error ()

IsGeneration (member, generation_number) Returns: boolean Extension: AS2005, AS2000

(Analysis Services and Essbase implement different semantics for this function, although the syntax is the same.)

In Analysis Services, this function returns true if the member is generation_ number steps from the leaf level, and false otherwise. The definition of a generation is as follows: The leaf level is considered to be generation 0. For every non-leaf member, the generation number is 1 plus the range of generation numbers from all of the children of its parent. In an irregular hierarchy, this means that a member may belong to more than one generation. For example, the generation numbers for a simple hierarchy are shown in Figure A-23.

Figure A-23: Microsoft Implementation of IsGeneration ().

In the case of a ragged level-based dimension, the generations are counted from the visible members. If a leaf member has a hidden parent and a visible grandparent, for example, the visible grandparent will be considered to be generation 1.

The expression IsGeneration ([Account].CurrentMember, 0) is equivalent to IsLeaf ([Account].CurrentMember).

See also: IsAncestor, IsSibling (), IsLeaf (), IsLevel () iif (), IS, .Ordinal

IsLeaf (Member) Returns: Boolean Extension: AS2005, AS2000

This function returns true if the Member is a leaf member in its dimension, whether the dimension is a parent-child dimension or a regular dimension.

In the case of a ragged level-based dimension, a member is considered to be a leaf member if it has no visible children.

Essbase accomplishes the same thing with IsLevel ().

IsSibling (Member1, Member2) Returns: boolean Extension: AS2005, AS2000

This function returns true if the Member1 is a sibling of Member2. In Analysis Services, IsSibling () considers a member to be a sibling of itself.

In Analysis Services, in a ragged, level-based dimension, the sibling relationship is determined by the visibility of members. If a parent has one hidden child and one visible child, and the hidden child has a visible child, the two visible children will be considered as siblings.

See also: IsAncestor, IsLeaf (), IsGeneration (), iif (), IS, .Ordinal

tuple[.Item](index) Returns: member Standard: AS2005, AS2000, Essbase

This function returns the member at the index position within the tuple. The index is based at 0. For example, ([Product].[Jackets], [Time].[2006]).Item (0) is [Product].[Jackets], and ([Product].[Jackets], [Time].[2006]).Item (1) is [Time].[2006]. We indicate that Item () is optional because it is the default operator. The following are equivalent:

     Tuple (index)     Tuple.Item (index) 

set[.Item](index) Returns: tuple Standard

set[.Item](string expression[ ,string expression … ]) Returns: tuple Standard

The first variation of the .Item () operator returns the tuple at the index position within the set. The index is based at 0. For example, consider:

     { [Time].[1996], [Time].[1997] }.Item (0) is [Time].[1996]     { [Time].[1996], [Time].[1997] }.Item (1) is [Time].[1997] 

The second variation returns the first tuple in the set whose name is matched by the string expressions. When using the string form, you can use either one string or more than one string. If you use one string, it must contain a complete tuple specification. If you use more than one string, then the number of strings must match the number of dimensions, but each string will identify only one member from one dimension. In either case, the order of dimensions listed in the string(s) must match the order of dimensions in the set. If some member from the strings is not found in the metadata when the expression is parsed, then a parse error results. If the member is found in the metadata, but not in any tuple in the set, then an empty tuple is returned. For example, the following two item specifications are identical:

     Crossjoin ([Time].[Year].members, _[Customer].[State].Members).Item (     "[1997]", "[FL]")     Crossjoin ([Time].[Year].members, _[Customer].[State].Members).Item (     "([1997], [FL])") 

Note that in the tuple specifications, member expressions can be used as well as named members. For example, the following are also equivalent to the two-item specifications just given:

     Crossjoin ( [Time].[Year].members, [Customer].[State].Members ).Item ( "[1998].lag (1)", "[FL]") Crossjoin ( [Time].[Year].members,      [Customer].[State].Members).Item ( "([1997].[Q1].Parent, [FL])") 

We indicate that .Item () is optional because it is the default operator. The following are equivalent:

     Set (index)     Set.Item (index) 

Remember: If you are trying to use Rank () to pick out an index for Item (), that Rank returns a 1-based index, and you will need to subtract 1 from it to use it with Item ().

K

KPICurrentTimeMember (KPI_name) Returns: member Extension: AS2005

This function returns the time member associated with the KPI named KPI_name. The KPI_Name is a string expression.

KPIGoal (KPI_name) Returns: member Extension: AS2005

This function returns the member that calculates the value of the goal for the KPI named KPI_name. The KPI_Name is a string expression.

KPIStatus (KPI_name) Returns: member Extension: AS2005

This function returns the member that calculates status value associated with the KPI named KPI_name. To conform to the conventions used in constructing the KPI graphic images, you should try to have this function return a value that is the KPIStatusValue () result somehow normalized between -1 and 1, although there is no technical requirement that you do so. The KPI_Name is a string expression.

KPITrend (KPI_name) Returns: member Extension: AS2005

This function returns the member that calculates a trend value associated with the KPI named KPI_name. To conform to the conventions used in constructing the KPI graphic images, you should try to have this function return a value normalized between -1 and 1, although there is no technical requirement that you do so. The KPI_Name is a string expression.

KPIValue (KPI_name) Returns: member Extension: AS2005

This function returns the member that calculates the value of the KPI named KPI_name. The KPI_Name is a string expression.

KPIWeight (KPI_name) Returns: member Extension: AS2005

This function returns the number that calculates weighting of the contribution of the KPI named KPI_name to its parent KPI. To conform to the conventions used in constructing the KPI graphic images, you should try to have this function return a value normalized between -1 and 1, although there is no other technical requirement that you do so. The KPI_Name is a string expression.

L

member.Lag (index) Returns: member Standard

.Lead () returns the member that is index number of members after the source member along the same level, and .Lag () returns the member that is index number of members before the source member on the same level. .Lead (0) and .Lag (0) each result in the source member itself. Lagging by a negative amount is the same as leading by the positive quantity and vice versa. Figure A-24 shows examples of .Lead () and .Lag ().

Figure A-24: .Lag () and .Lead ().

member.LastChild Returns: member Standard

See definition for .FirstChild

LastPeriods (index [, member ]) Returns: set Standard

This function returns the set of index periods from member back to the member lagging by index-1 from member. This is almost equivalent to

         { member.LAG (index - 1) : member }. 

If member is not specified, then it defaults to the current member of the Time-typed dimension in the cube. If the index is a negative number, then the range goes forward from the member to index -1 members instead of backward. If index is 0, then an empty set is returned (which makes it slightly different from using .Lag ()). If member is omitted, and no dimension in the cube is marked as being Time-typed, the statement will be parsed and execute without error. However, when a client attempts to retrieve a cell calculated in part by the LastPeriods () function, a cell error will occur.

The behavior of LastPeriods () is shown in Figure A-25.

Figure A-25: Behavior of LastPeriods ().

See also: OpeningPeriod (), ClosingPeriod (), .Lag (), .Lead ()

member.LastSibling Returns: member Standard

See definition for .FirstSibling

member.Lead (index) Returns: member Standard

See definition for .Lag ()

Leaves () Returns: set

Leaves (dimension) Returns: set

This function returns a set of the cross-join of the lowest level of all attribute hierarchies in the dimension. This includes the dimension's key attribute and all leaf-level attributes. If the dimension is omitted, the leaf level space is a set for the entire leaf level of the cube (!).

Note that the Leaves () function cannot be used if different measure groups in scope in the cube use the dimension at different levels of granularity (including if some are dimensioned by it and some do not). You can select from a subcube that only includes measures from a suitable measure group or groups.

While you can use this function in any MDX expression, it is most likely to be useful as part of specifying subcubes either in an MDX script or in a query or session subcube.

See also: MeasureGroupMeasures (), Root ()

member.Level Returns: level Standard

This function returns a member's level.

Levels (string expression) Returns: level Extension: AS2005, AS2000

This function returns the level whose name is given by string expression. It is typically used with user-defined functions (UDFs) that return a name. The string expression can be any expression that results in a level reference. For example, the string "[Time].[Year]" will result in the year level of the Time dimension. However, the string "[Time].Levels (1)" in a Time dimension where the year level is the first one down from the root level will also result in the year level. (See the following description for the Dimension.Levels () function as well.)

Dimension.Levels (numeric expression) Returns: level Standard

This function returns the dimension level specified by numeric expression. Note that in Analysis Services, the number is zero-based, starting at the root level, while in Essbase the number is one-based starting at the leaf level.

For example, in Analysis Services, if the levels of the [Time] dimension are [All], [Year], and [Month], then [Time].Levels (0) returns the [Time]. [All] level, and [Time].Levels (2) returns the [Time].[Month] level. In Analysis Services, you can obtain the number of levels in the dimension with Dimension.Levels.Count, which lets you refer to the leaf level by the expression Dimension.Levels (Dimension.Levels.Count).

LinkMember (member, dimension) Returns: member Extension: AS2005, AS2000

The LinkMember () function is used to reference a member in one hierarchy based on a member from another related hierarchy. The hierarchies may either be from the same dimension (where a dimension has multiple hierarchies) or from different dimensions. (Remember that different hierarchies are different dimensions in Microsoft OLAP/Analysis Services.) The members are matched by key rather than by name, so members with the same key but with different names will be linked. For example, the expression

     Hierarchize (        Ascendants (           Linkmember ([Time].[Calendar].[Jan 1 1999],[Time].[Fiscal])     )) 

will return the ascendants in the fiscal hierarchy for the calendar hierarchy member [Jan 1 1999].

LinRegIntercept (set, y numeric expression [, x numeric expression]) Returns: number

Standard

This function returns the intercept of the linear regression line calculated from the given data points (where the regression line intersects 0). For the linear equation y = ax + b, which will be determined over some set of y and x, the values of the y numeric expression are evaluated over the set to get the y values. If the x numeric expression is present, then it is evaluated over the set to get the values of the x axis. Otherwise, the cells formed by the set are evaluated within the current context and their values are used as the x values. Empty cells and cells containing text or logical values are not included in the calculation, but cells with zero values are included.

Once the linear regression line has been calculated, this function returns the x-intercept of the line (represented by b in the equation y = ax + b).

See also the other LinRegXXX functions.

LinRegPoint (x slice numeric expression, set, y numeric expression [, x numeric expression]) Returns: number

Standard

This function returns the value of the calculated linear regression line y = ax + b for a particular value of x. For the linear equation y = ax + b, which will be determined from a set of y and x values, the values of the y numeric expression are evaluated to get the y values. If the x numeric expression is present, then it is evaluated over the set to get the values of the x axis. Otherwise, the cells formed by the set are evaluated within the current context and their values are used as the x values. Empty cells and cells containing text or logical values are not included in the calculation, but cells with zero values are included.

Once the linear regression line has been calculated, the value of y = ax + b is calculated for the value given in the x slice numeric expression and is returned.

LinRegR2 (set, y numeric expression [, x numeric expression]) Returns: number Standard

This function returns the statistical R² variance of the given data points to the linear regression line calculated from them. For the linear equation y = ax + b, which will be determined over some set of y and x, the values of the y numeric expression are evaluated to get the y values. If the x numeric expression is present, then it is evaluated over the set to get the values of the x axis. Otherwise, the cells formed by the set are evaluated within the current context and their values are used as the x values. Empty cells and cells containing text or logical values are not included in the calculation, but cells with zero values are included.

Once the linear regression line has been calculated, this function returns the statistical R² variance between the points on it and the given points.

See also the other LinRegXXX functions.

LinRegSlope (set [, y numeric expression [, x numeric expression]) Returns: number

Standard

This function returns the slope of the linear regression line calculated from the given data points. For the linear equation y = ax + b, which will be determined over some set of y and x, the values of the y numeric expression are evaluated to get the y values. If the x numeric expression is present, then it is evaluated over the set to get the values of the x axis. Otherwise, the cells formed by the set are evaluated within the current context and their values are used as the x values. Empty cells and cells containing text or logical values are not included in the calculation, but cells with zero values are included.

Once the linear regression line has been calculated, this function returns the slope of the line (represented by a in the equation y = ax + b).

See also the other LinRegXXX functions.

LinRegVariance (set, y numeric expression [, x numeric expression]) Returns: number

Standard

This function returns the variance of fit of the calculated linear regression line to the actual points given for it. For the linear equation y = ax + b, which will be determined over some set of y and x, the values of the y numeric expression are evaluated to get the y values. If the x numeric expression is present, then it is evaluated over the set to get the values of the x axis. Otherwise, the cells formed by the set are evaluated within the current context and their values are used as the x values. Empty cells and cells containing text or logical values are not included in the calculation, but cells with zero values are included.

Once the linear regression line has been calculated, this function returns the statistical variance between its points and the given points.

See also the other LinRegXXX functions.

LookupCube (cube_string, numeric_expression) Returns: number Extension: AS2005, AS2000

LookupCube (cube_string, string_expression) Returns: string Extension: AS2005, AS2000

LookupCube () can be used to retrieve a single value from another cube. This function can look up values from a regular cube or a virtual cube. The expression can also reference calculated members within the designated cube. The function is most likely to be used as part of a calculated member or custom rollup expression, although care must be taken to ensure that the result is as expected, because LookupCube () returns only a single value and does not respect the context of the current query. This means that any necessary current members need to be placed in the numeric expression or string expression. For example, the following calculated member only makes sense if we are looking at the All level on the other dimensions:

     WITH MEMBER [Measures].[Store Net Sales] AS     '[Measures].[Store Sales] - LookupCube ("Budget","[Account].[Total     Expense]")' 

The following will include time and product dimensions from the sales cube:

     WITH MEMBER [Measures].[Store Net Sales] AS     '[Measures].[Store Sales] - LookupCube ("Budget",       "([Account].[Total Expense]," + [Time].CurrentMember.UniqueName +     ","       + [Product].CurrentMember.UniqueName + ")"     )' 

See also: StrToVal ()

M

Max (set [, numeric expression]) Returns: number Standard

This function returns the maximum value found across the cells of the set. If a numeric expression is supplied, then the function finds the maximum of its nonempty values across the set. Note that in Analysis Services, a positive number divided by zero will cause an erroneous value that will be reported as the maximum.

See also: Min (), Median ()

MeasureGroupMeasures (string_expression) Returns: set Extension: AS2005

This function returns the set of measures contained in the measure group named by string_expression. Note that the name should be exactly as specified when designing the cube, e.g. "Sales" or "Currency Rates", not "[Sales]" or "[Currency Rates]."

See also: .Members, .AllMembers

Median ( set [, numeric expression ]) Returns: number Standard

This function returns the median value found across the cells of the set. If a numeric expression is supplied, then the function finds the median of its values across the set.

See also: Min (), Max ()

dimension.Members Returns: set Standard

hierarchy.Members Returns: set Standard (not in Essbase 9)

level.Members Returns: set Standard

Each of the variations of the .Members function returns the set of all members within the scope of the given metadata object in the database's default order. Figure A-26 shows the scope of the members operator. Dimension.Members, shown in Figure A-26a, returns the members of the entire dimension and includes the All member of the hierarchy if present. Because in OLAP/Analysis Services a hierarchy is implemented as a dimension, the Hierarchy.Members function is also shown. Level.Members, shown in Figure A-26b, selects all members in the specified level.

Figure A-26a: Members selected by .Members operator.

Figure A-26b: Members selected by Level.Members

See also: .AllMembers, MeasureGroupMeasures (), AddCalculatedMembers (), StripCalculatedMembers ()

Members (string expression) Returns: member Extension: AS2005, AS2000

This function returns the member whose name is given by string expression. (Yes, it only returns a single member, even though its name is plural.) The most common use for this function is to take a string from a user-defined function (UDF) that identifies a member and convert it to a member. For example, consider a UDF named UDF_GetMySalesTerritory on the client that returned the member name for the user's sales territory. Given this UDF, the following expression,

     ([Measures].[Sales], Members ( UDF_GetMySalesTerritory () ) ) 

would refer to the sales value for that user's sales territory.

See also: StrToMember (), StrToTuple (), StrToSet (), .Name, .UniqueName

MemberToStr (member) Returns: string Standard

This function returns the unique name of a member. In Analysis Services, the MDX Compatibility and MDX Unique Name Style connection parameters will determine the format of the name generated. This is identical in function to .UniqueName.

See also: Member.UniqueName

Min (set [, numeric expression]) Returns: number Standard

This function returns the minimum value found across the cells of the set. If a numeric expression is supplied, then the function finds the minimum of its values across the set. Note that in Analysis Services, a negative number divided by zero will cause an erroneous value that will be reported as the minimum.

See also: Max (), Median ()

MTD ([member]) Returns: set Standard

MTD () is the equivalent of PeriodsToDate () with the level set to Month. If member is not specified, it defaults to the current member of the Time typed dimension. If no Time-typed dimension is in the cube, or if it does not have a level tagged as Month, then an error results.

See also: PeriodsToDate (), YTD (), QTD (), WTD ()

N

dimension.Name Returns: string

Extension: AS2005, AS2000

This function returns the name of the dimension.

See also: .UniqueName, Dimensions ()

hierarchy.Name Returns: string Extension: AS2005, AS2000

This function returns the name of the hierarchy.

See also: .UniqueName

level.Name Returns: string Extension: AS2005, AS2000

This function returns the name of the level.

See also: .UniqueName, .Ordinal, Levels ()

member.Name Returns: string Extension: AS2005, AS2000

This function returns the name of the member. (Essbase uses the predefined member property member.[MEMBER_NAME] to achieve the same effect.)

See also: StrToMember (), StrToTuple (), StrToSet (), TupleToStr (), .UniqueName

NameToSet (membername) Returns: set (of one member) Extension: AS2005, AS2000

This function returns a set containing one member specified by the member name. If no member can be found with this name, then the set is returned empty (and it cannot be identified with .Dimension). The contents of membername must be only a member name or unique name. It cannot be a member expression, as StrToSet () would allow.

See also: Member.UniqueName, StrToSet (), StrToMember ()

member.NextMember Returns: member Standard

See description of .PrevMember

NonEmpty (set1 [, context_set]) Returns: set Extension: AS2005

This function returns the tuples of set1 that are non-empty across the tuples of context_set. Non-emptiness is a characteristic of cells, not tuples. The measure(s) to use in determining whether or not a tuple is "empty" are found in one of the sets. If the context_set is omitted, the current context of all current members from all dimensions not part of set1 is used. Even if context_set is provided, the current context of all attributes is present, whether or not they explicitly participate in either of the sets.

See also: NonEmptyCrossJoin (), Filter (), Count (), NonEmptyCount (), IsEmpty ()

NonEmptyCrossJoin (set1, set2 [,set3 … ] [, set-count]) Returns: set Extension: AS2005, AS2000

This function returns the nonempty cross-join of two or more sets. It is based on data actually present in fact tables. This means that it filters out all tuples involving calculated members.

Note that while this function is supported in Analysis Services 2005, Microsoft is recommending that you use NonEmpty instead.

Nonemptiness is a characteristic of cells as opposed to tuples. NonEmptyCrossJoin () takes a different approach to specifying the cells than the other functions that deal with empty/nonempty cells associated with tuples. If the set-count is present, then the number specified for it will be used as the number of sets (starting at set1) to actually cross-join. The remaining sets listed will be used to form the slices used to find the cells that are nonempty. (Any dimensions not listed will have their current member used to determine cells.)

If the set-count parameter is provided, then only that number of sets (in the order that they appear) will contribute tuples to the resulting set. The remaining sets will provide the context or add members for consideration in the nonemptiness. The other sets may have only one member, or they may have multiple members. If they have multiple members, it is possible that more than one contributes to result tuples. Only the distinct tuples from the dimensions listed in the first set-count sets will be returned, though.

Note that if a measure field is NULL in the underlying fact table, Analysis Services 2000 will treat the measure as zero, so the associated tuple will show up in the nonempty set (unless all measures in the row are NULL). Analysis Services 2005 allows measures to be NULLable, however, so the tuple won't show up unless there actually was a value for it in the underlying table.

Note that NonEmptyCrossJoin () always eliminates duplicate tuples, and ignores all calculations by calculated members, cell calculations, MDX scripts, and so on. This makes it less useful than NonEmpty () for determining true non-emptiness of a set of tuples, so in general NonEmpty () seems preferable.

See also: CrossJoin (), Extract (), Except (), Union (), Intersect ()

NOT expr Returns: Boolean Standard

The NOT operator returns false if expr is true, and false otherwise.

O

OpeningPeriod ([level [, member]]) Returns: member Standard

ClosingPeriod ([level [, member]]) Returns: member Standard

The OpeningPeriod () and ClosingPeriod () functions are essentially first-descendant and last-descendant operators that are intended primarily to be used with the Time dimension, though they may be used with any dimension. The OpeningPeriod function returns the first member among the descendants of member at level. For example, OpeningPeriod (Month, [1991]) returns [January, 1991]. If no member is specified, then the default is the current member of the Time-type dimension in that cube. If no level is specified, then it is the level immediately below that of member. OpeningPeriod (level, member) is equivalent to Descendants (member, level).Item (0). ClosingPeriod () is very similar, only it returns the last descendant instead of the first descendant. Opening-Period () and ClosingPeriod () are illustrated in Figure A-27.

Figure A-27: OpeningPeriod () and ClosingPeriod ().

If member is omitted, and no dimension in the cube is marked as being Time-typed, the statement will parse and execute without error. However, when a client attempts to retrieve a cell calculated in part by the OpeningPeriod () or ClosingPeriod () function, a NULL member reference will occur in Analysis Services.

See also: PeriodsToDate (), ParallelPeriod (), Is

expr1 OR expr2 Returns: Boolean Standard

The OR operator returns true if either expr1 is true or expr2 is true. In Analysis Services, if expr1 evaluates to true, then expr2 is not evaluated (because the result is already guaranteed to be true).

See also: AND, NOT, XOR, iif (), Filter ()

Order (set, {string_expression| numeric_expression} [,ASC | DESC | BASC | BDESC]) Returns: set

Standard

Order () returns the set that it is given after it sorts it based on the given expression. If a numeric or string value expression is provided, then that is used to sort the tuples; otherwise, the values of the cells in context are used. This function also takes an optional flag to indicate how to sort. The default ordering is ASC (ascending without breaking the hierarchy).

Order () has two modes for sorting: breaking hierarchy and preserving hierarchy. The BASC and BDESC options break the hierarchy, while ASC and DESC do not. When the hierarchy is broken, the values associated with each tuple in the set are treated as peers, and the set is ordered only by the values. When the hierarchy is preserved, a more complex ordering algorithm is used, which can lead to very useful results.

Note that there is no explicit way to sort a set based on more than one criterion. For example, if you want to sort a set based primarily on a string member property and secondarily on a numerical value, no good way is available for specifying this.

Preserving Hierarchy: Set Containing One Dimension

When the set consists only of one dimension's worth of members, sorting and preserving the hierarchy orders each parent before its children. At each level of members from the top down, the children of each parent are sorted relative to each other. For example, the product hierarchy for a fictional fishcake manufacturer is shown in Figure A-28 and the units shipped per product are shown in Figure A-29. Ordering these members while preserving the hierarchy would give us the orderings shown in Figure A-30.

Category	Product Name
Premium	Ancient Mariner
Premium	Gobi Crab Cakes
Premium	Moby Dick
Premium	Neptunes Glory
Diet	Silver Scales
Diet	Thin Fins
Standard	Anglers Choice
Standard	Briny Deep
Standard	Gill Thrill
Standard	Mako Steak-o

Figure A-28: Sample product hierarchy.

Product	Units
Ancient Mariner	221,871
Gobi Crab Cakes	223,351
Moby Dick	200,745
Neptunes Glory	210,745
Premium	856,274
Silver Scales	425,604
Thin Fins	434,482
Diet	860,086
Anglers Choice	207,662
Briny Deep	201,443
Gill Thrill	209,962
Mako Steak-o	215,521
Standard	834,588

Figure A-29: Units shipped in hierarchy.

ASC

Product	Units
Standard	834,588
Briny Deep	201,443
Anglers Choice	207,662
Gill Thrill	209,962
Mako Steak-o	215,521
Premium	856,274
Moby Dick	200,745
Neptunes Glory	210,745
Ancient Mariner	221,871
Gobi Crab Cakes	223,351
Diet	860,086
Silver Scales	425,604
Thin Fins	434,482

DESC

Product	Units
Diet	860,086
Thin Fins	434,482
Silver Scales	425,604
Premium	856,274
Gobi Crab Cakes	223,351
Ancient Mariner	221,871
Neptunes Glory	210,745
Moby Dick	200,745
Standard	834,588
Mako Steak-o	215,521
Gill Thrill	209,962
Anglers Choice	207,662
Briny Deep	201,443

Figure A-30: Hierarchy preserved in ordering.

Also, an extra sophistication in the sorting process is not immediately evident. Let us imagine that the category-level members [Standard], [Premium], and [Diet] were not part of the set being queried, while the ProductName members still were. Therefore, the category-level [Units] value does not come directly into play when the set is ordered. However, when sorting without breaking hierarchy, the [Units] value is still calculated at each parent member when Microsoft OLAP Services is trying to figure out how to order the groups of children relative to their cousins.

For example, suppose that the following set of product names was ordered by Units: {[Product].[Briny Deep], [Product].[Anglers Choice], [Product]. [Ancient Mariner], [Product].[Gobi Crab Cakes], [Product].[Thin Fins]}. The ordering shown in Figure A-31 would be returned.

ASC

Product	Units	Parent's Units
Briny Deep	201,443	834,588
Anglers Choice	207,662
Ancient Mariner	221,871	856,274
Gobi Crab Cakes	223,351
Thin Fins	434,482	860,086

DESC

Product	Units	Parent's Units
Thin Fins	434,482	860,086
Gobi Crab Cakes	223,351
Ancient Mariner	221,871	856,274
Anglers Choice	207,662
Briny Deep	201,443	834,588

Figure A-31: Hierarchy preserved when ordering a set without parents.

Preserving Hierarchy: Set Containing Multiple Dimensions

When the set consists of multiple dimensions, the tuples are sorted so that the hierarchical ordering of the first dimension in the tuples is the primary ordering. According to this ordering, within each member of the first dimension, the members of the second dimension are sorted. Within each ([member from dim 1], [member from dim 2]) tuple, the members of the third dimension are sorted, and so on. For example, let's expand the example to include some customers and time periods and order the cross-join of

     { [Product].[Briny Deep], [Product].[Anglers Choice], [Product].[Mako     Steak-o] } 

with

     { [Time].[Quarter 2], [Time].[Quarter 3] } 

with

     { [Customer].[Supernaturalizes Food Service], [Customer].[Hanover     Distributors], [Customer].[Subcommittees Anticipates Farms] }. 

The ordering and values shown in Figure A-32 will appear. The products are arranged in order of decreasing quantity over year and customer parent. For each product, the quarters are arranged in order of decreasing quantity based on that product and customer parent. For each (Product, Time) tuple, the customers are arranged in order of decreasing quantity. Where tuples are tied (at the blank cells), the original ordering of the tuples is retained rather than the dimension's ordering (which was alphabetical).

			Qty.
Mako Steak-o	Quarter 2	Subcommittee Anticipation Farms	199.00
		Supernatural Food Service	87.00
		Hanover Distributions	64.00
	Quarter 3	Hanover Distributions	185.00
		Supernatural Food Service	151.00
		Subcommittee Anticipation Farms	105.00
Anglers Choice	Quarter 3	Hanover Distributions	181.00
		Supernatural Food Service	179.00
		Subcommittee Anticipation Farms
	Quarter 2	Supernatural Food Service	127.00
		Hanover Distributions	73.00
		Subcommittee Anticipation Farms
Briny Deep	Quarter 3	Subcommittee Anticipation Farms	213.00
		Supernatural Food Service
		Hanover Distributions
	Quarter 2	Subcommittee Anticipation Farms	204.00
		Supernatural Food Service
		Hanover Distributions

Figure A-32: Hierarchy preserved when ordering a set with multiple dimensions.

See also: Hierarchize ()

Level.Ordinal Returns: number (integer) Standard

This function returns the index of the level in the cube. The root level of a cube is number 0, the next level down (if there is one) is number 1, and so on. This is typically used in conjunction with IIF () to test whether a cell being calculated is at, above, or below a certain level in the cube (for example, below the All level or below the Quarter level). In Analysis Services, you can obtain the number of levels in the dimension with Dimension.Levels.Count.

See also: Is, .Name, Dimension.Levels (), Levels ()

P

ParallelPeriod ([level [, index [, member]]]) Returns: set Standard

This function is similar to the Cousin () function. It takes the ancestor of member at level (call it "ancestor"), then it takes the sibling of ancestor that lags by index (call it "in-law"), and it returns the cousin of member among the descendants of in-law. Figure A-33 illustrates the process of finding the parallel period. ParallelPeriod (level, index, member) is equivalent to Cousin (member, Ancestor (Member, Level).Lag (index).

Figure A-33: ParallelPeriod () operator.

See also: Cousin (), OpeningPeriod (), ClosingPeriod (), Is

member .Parent Standard

This function returns the source member's parent member, if it has one. The behavior of Parent () is shown in Figure A-34.

Figure A-34: Behavior of .Parent.

See also: Ancestor (), Ascendants (), IsAncestor (), IsGeneration ()

PeriodsToDate ([level[, member]]) Returns: set Standard

This function returns a set of members at the same level as member, starting at the first descendant under member's ancestor at level and ending at member. If neither level nor member is specified, then the default member is the current member of the cube's Time-typed dimension, and level is the parent level of that member. If the level is specified but the member is not, then the dimension is inferred from the level, and the current member on that dimension is used. The function is identical to the following:

     { Descendants (Ancestor (member, level), member.Level).Item (0)     : member } 

If member is omitted, and no dimension in the cube is marked as being Time-typed, the statement will be parsed and execute without error. However, when a client attempts to retrieve a cell calculated in part by the PeriodsToDate () function, a cell error will occur.

The behavior of PeriodsToDate () is shown in Figure A-35.

Figure A-35: Behavior of PeriodsToDate ().

See also: .Siblings, OpeningPeriod ()

Predict (mining_model_name, numeric_mining_expression) Returns: Number Extension: AS2005, AS2000

Predict () evaluates the given numeric_mining_expression against the data-mining model identified by mining_model_name. The actual syntax of the numeric_mining_expression is not part of MDX, but part of Microsoft's OLE DB for Data Mining specification.

member.PrevMember Standard

member.NextMember Standard

.PrevMember gives the previous member along the level implied by the member, while .NextMember gives the next member along the level implied by the member. Figure A-36 shows examples of .PrevMember and .NextMember. Note that these functions return the next or the previous member within the same level regardless of whether the new member shares the same parent or not.

Figure A-36: NextMember and .PrevMember.

See also: OpeningPeriod (), ClosingPeriod (), Is

member.Properties ( property name ) Returns: string Standard

member.Properties (property name, TYPED) Returns: various Extension: AS2005

Returns the value of the named property at the member. The property name can be a string expression. If it is, the name expression is evaluated cell by cell every time the property reference is.

Even though Analysis Services 2000 and 2005 support member properties in a variety of data types, the return value of the .Properties () function is coerced to be a string unless you include the TYPED flag. Then, the property value is returned in its internal data type.

In Analysis Services, every member has associated properties named CAPTION, NAME, ID, and KEY.

See also: .PropertyName, .MemberValue, StrToMember (), StrToSet (), StrToTuple (), StrToValue (), Members (), Dimensions (), Levels ()

member.PropertyName Returns: string, number, boolean Standard (except AS2000, AS2005)

In Essbase, a property value associated with a member is referenced by member.PropertyName, where the name of the property is literally placed in the MDX. For example, the following retrieves the ClubStatus property value for the current Customer member:

     [Customer].CurrentMember.[ClubStatus] 

Since the syntax for this kind of property reference is similar to the syntax for a number of functions, you should always use delimited names (in []) for property names.

See also: .Properties (), .MemberValue, StrToMember (), StrToSet (), StrToTuple (), StrToValue (), Members (), Dimensions (), Levels ()

Q

QTD ([member]) Returns: set Standard

QTD () is the equivalent of PeriodsToDate () with the level set to Quarter. If member is not specified, it defaults to the current member of the Time-typed dimension. If no Time-typed dimension is in the cube, or if one is in the cube without a level tagged as Quarter, then an error results.

See also: PeriodsToDate (), YTD (), MTD (), WTD ()

R

Rank (tuple, set) Returns: number (integer) Standard

Rank (tuple, set, numeric_expression) Returns: number (integer) Extension: AS2005, AS2000

This function returns the (one-based) index of the tuple in the set. If the tuple is not found in the set, Rank () returns 0.

If the optional numeric expression is provided, then it is evaluated for tuple. In AS2005, when this expression is found, the given ordering of the set is ignored. Instead, AS2005 puts the tuples of the set in ascending order according to numeric_expression, and returns the tied rank number according to that numbering. In AS2000, the numeric expression is evaluated for the neighbors of tuple in the ordering of the set as it is passed to Rank (). If two or more tuples share the same value in the set, then the rank number returned is the tied rank. Note that if the set is not sorted by the same numeric expression, then the rank numbers will reflect the (possibly tied) rank according to the set as it is actually sorted.

MEMBER	SALES	UNITS
Leather Jackets	100	5
Leather Pants	120	4
Leather Gloves	150	200
Leather Bags	150	16
Leather Skirts	200	4

Consider the following examples against a simple set of numbers:

Against this set of tuples (which we will call Set1) and associated values, the following is true:

Rank ([Product].[Leather Pants], Set1) is 2.

Rank ([Product].[Leather Bags], Set1, [Measures] .[Sales]) is 3 (tied with leather gloves).

Rank ([Product].[Leather Skirts], Set1, [Measures] .[Units]) is 5 in AS2000 (the tie with leather pants is not noticed). In AS2005, it is 1 (tied for first place when sorted in ascending order).

Note that the .Item () and Subset () functions use a zero-based index; the rank of Set.Item (0) is 1.

See also: .Item (), Subset (), Head (), Tail ()

RollupChildren (member, string expression) Returns: number Extension: AS2005, AS2000

This function is used to return the value generated by rolling up the children of a specified parent member using the specified unary operator. The string expression is evaluated once per child of member. You can use a constant string value for the expression, as well as a string value that changes with each member. The first (or only) unary operator may be one of +, −, ~, or a number, while subsequent operators may be one of +, −, ⊛, /, ~ or a number. When a number is used, it is a weighting value; the effect is to multiply the cell value related to the child member by the number, and add it to the accumulating rollup value. Frequently, a reference to a member property ([Dimension].CurrentMember.Properties ("Some Property")) will be the string expression. You may also use a string expression based on a property. For example, the following expression will create the positive sum of all children that would ordinarily be subtracted from the sum:

     iif ([Accounts].CurrentMember.Properties ("UNARY_OPERATOR") <> "-",       "~",       "-"     ) 

This function could be used, for example, in a budgeting application where there may be more than one way to roll up the Accounts dimension, and perhaps some costs are ignored in the alternate rollup. You could create a member property "alternate operators" to hold the operators of this alternate rollup. The following expression would return the results of this alternate rollup (note that the current member is evaluated once per child of [Account].[Net Profit]):

     RollupChildren ([Account].[Net Profit],          [Account].CurrentMember.Properties ("ALTERNATE_OPERATORS") ) 

Note that if you use this function as a custom rollup operator (for example, in a local cube), you may need to use it in conjunction with an iif () test and the .Ignore function to avoid infinite recursion at leaf-level members.

See also: Sum ()

Root () Returns tuple Extension: AS2005

Root (dimension) Returns tuple Extension: AS2005

Root (tuple) Returns tuple Extension: AS2005

The Root () function returns a tuple of the root attribute-dimension members for each attribute hierarchy in the scope. If an attribute dimension does not have an All member, then the default member is included instead. It may return an empty or null tuple, as described below.

If the argument is a dimension, then all the related attribute dimensions/ hierarchies for that dimension are included. In this case, it does not matter whether you pick the overall dimension (for example, [Product]) or a hierarchy within the dimension (for example, [Product].[ByCategory] or [Product].[Ship Weight]).

If the argument is a tuple, then the result tuple contains the original members and all of the root members for the other attribute hierarchies in the respective dimensions. Note that the result tuple puts the members in a server-defined order, not the dimension order of members in the tuple. For example, you may have a tuple such as

     ( [Product].[Shipweight].[12],       [Time].[YQMD].[Oct, 2005] ) 

but if Time appears before Product in the order of dimensions in the cube designer, the time components of the result tuple will appear first. However, the Shipweight member will be [12] and the YQMD hierarchy member will be [Oct, 2005]. The tuple can contain a member from different attribute hierarchies of a single logical dimension, like [Time] or [Product], but if the members do not have a corresponding tuple in the dimension, the result tuple is null. If they do, each of the members is retained in the tuple.

If this function is called with no argument, a tuple composed of the root members for each hierarchy is returned.

Note that this function can be used anywhere, not just in MDX scripts.

See also: Leaves ()

S

Scope

Extension: AS2005

Scope is an MDX Scripting statement that defines a subcube, within which the actions of other statements is limited. The general syntax is:

     Scope subcube ;        statement1 ; [ ... statementN ; ]     End Scope ; 

SetToArray (set [, set … ][, numeric or string expression])

Standard

The SetToArray () function creates an array as a COM Variant type that holds an array of values. The only use for this function in OLAP and Analysis Services is to pass the constructed array to an external function that is defined as taking an array.

The constructed array will hold values of only one type (which might be, for example, long integer, single float, double float, or string). That type is determined by the type of the first value that is actually placed into the array. The dimensionality of the array that is created is determined by the number of sets that appear as arguments to SetToArray (). If the optional numeric or string expression is provided, it is evaluated over the cross-join of the sets, and the values are placed in the array. If the numeric or string expression is not provided, then the cross-join of the sets is evaluated in the current context, and the results obtained are placed in the array.

SetToStr (set) Returns: string Standard

This function constructs a string from a set. It will frequently be used to transfer a set to an external function that knows how to parse the string, even though the string is syntactically suitable for OLAP Services to parse into a set. OLAP Services constructs the string as follows: The first character is { and the last character is }. Between the braces, each tuple is listed in order. A comma and a space separate each tuple from the next name. If the set contains only one dimension, then each member is listed using its unique name. If the set contains more than one dimension, then each tuple begins with an open parenthesis ["("] and ends with a closing parenthesis, ")." The unique name of the member from each dimension is listed in the order of the dimensions in the set, separated by a comma and a space. For example, in a Time dimension that has three years, the expression

     SetToStr ([Time].[Year].Members) 

would yield the following string:

     "{[Time].[All Times].[1998], [Time].[All Times].[1999], [Time].[All     Times].[2000]}" 

Moreover, the expression

     SetToStr ( {([Time].[1998], [Customer].[Northeast]), ([Time].[1999],     [Customer].[Southwest])} ) 

yields the following string:

     "{([Time].[All Times].[1998], [Customer].[All Customers].[Northeast]),     ([Time].[All Times].[1999], [Customer].[All Customers].[Southwest])}". 

Fairly large strings (greater than 16K) will take significant time to create, and the first release of OLAP Services was released with problems that led to the truncation of strings. The further down the hierarchy the members are, the longer and more numerous their unique names are likely to be. So, you may need to perform your own performance evaluations when using this function.

The style of the unique names generated into the string by this function will be affected by the MDX Unique Name Style and MDX Compatibility settings.

See also: Generate (), StrToValue (), StrToSet (), StrToMember (), LookupCube ()

Member.Siblings Returns: set Extension: AS2005, AS2000

This function returns the set of metadata siblings of a specified member in the database's default order. The resulting set includes the specified member itself. It does not include calculated members. Figure A-37 diagrams the selection of .Siblings.

Figure A-37: Diagram of .Siblings.

See also: .Children, .FirstSibling, .LastSibling

StdDev (set [, numeric value expression]) Returns: number Standard

StdDevP (set [, numeric value expression]) Returns: number Extension: AS2005, AS2000, Essbase

StDev (set [, numeric value expression]) Returns: number Extension: AS2005, AS2000

StDevP (set [, numeric value expression]) Returns: number Extension: AS2005, AS2000

These functions return the standard deviation of a numeric expression evaluated over a set. If the numeric value expression is not supplied, these functions evaluate the set within the current context to determine the values to use. The formula for obtaining the standard deviation is as follows:

StDev () calculates the sample standard deviation and uses the unbiased formula for population (dividing by n - 1 instead of n). On the other hand, StDevP () calculates the population standard deviation and uses the biased formula (dividing by n). StdDev () and StdDevP () are aliases of StDev () and StDevP (), respectively.

StripCalculatedMembers (set) Returns: set Extension: AS2005, AS2000

The StripCalculatedMembers () function returns the members of set after removing all the calculated members. The set is limited to only one dimension. Note that this function removes all calculated members defined, whether they were defined by CREATE MEMBER at the server or at the client, or in the query through WITH MEMBER.

See also: AddCalculatedMembers (), .AllMembers

StrToMember (string_expression) Returns: member Standard

StrToMember (string_expression, CONSTRAINED) Returns: member Extension: AS2005

This function refers to a member identified by the string_expression. This will frequently be used along with external functions to convert a string returned by the external function to a member reference within the query. The string expression can be dynamic as well as a fixed string. When the CONSTRAINED flag is omitted, the expression can resolve to an MDX expression that evaluates to a member as well as just the name of a member When the CONSTRAINED flag is present, the string_ expression can still be a string expression instead of a literal string, but when the string is evaluated, it must be a qualified or unqualified member name, or else the evaluation results in an error. If the error occurs in evaluating a slicer or axis, the query execution will stop. If the error occurs in evaluating a result cell, the cell will have an error result.

See also: Members (), StrToTuple (), StrToSet (), .Properties (), IsError ()

StrToSet (string_expression) Returns: set Standard

StrToSet (string_expression, CONSTRAINED) Returns: set Extension: AS2005

This function constructs a set from a string expression. This will frequently be used to transfer a set specification returned by a UDF back to the MDX statement. When the CONSTRAINED flag is omitted, the string must be a syntactically valid MDX set specification relative to the cube in whose context it is executed. For example, the set of all years in a Time dimension that has three year-level members could be created by passing either of the following strings into StrToSet:

     "{[Time].[All Times].[2004], [Time].[All Times].[2005],     [Time].[All Times].[2006]}"     "[Time].[Year].Members" 

When the CONSTRAINED flag is provided, the string expression must contain either a valid MDX tuple composed of named members, or a set of tuples composed of named members and enclosed by {}. Of the two examples above, the first would be allowed with the CONSTRAINED flag while the second one would not. If the error occurs in evaluating a slicer or axis, the query execution will stop. If the error occurs in evaluating a result cell, the cell will have an error result.

See also: StrToTuple, SetToStr (), TupleToStr (), Members (), .Properties (), .Name, .UniqueName, IsError ()

StrToTuple (string_expression) Returns: tuple Standard

StrToTuple (string_expression, CONSTRAINED) Returns: tuple Extension: AS2005

This function constructs a tuple from a string expression. This will frequently be used to transfer a tuple specification that is returned by an external function back to the MDX statement. The string must be a syntactically valid MDX tuple specification relative to the cube in whose context it is executed. When the CONSTRAINED flag is omitted, the string_expression can contain any MDX expression that results in a tuple. For example, the following two strings would give identical results in the customer dimension, where [AZ] is a child of [Southwest], in that both would result in the Southwest region member:

     "([Customer].[Southwest],[Time].[2006])"     "([Customer].[AZ].Parent,[Time].[2006])" 

When the CONSTRAINED flag is provided, the string expression must contain valid MDX tuple composed of named members, or else an error will result. Of the two examples above, the first would be allowed with the CONSTRAINED flag while the second one would not. If the error occurs in evaluating a slicer or axis, the query execution will stop. If the error occurs in evaluating a result cell, the cell will have an error result.

See also: StrToSet (), SetToStr (), TupleToStr (), Members (), .Properties (), .Name, .UniqueName, IsError ()

StrToValue (string expression) Returns: number or string Extension: AS2005, AS2000

StrToValue (string_expression, CONSTRAINED) Returns: number or string Extension: AS2005

This function takes the results of an arbitrary string expression and evaluates it as an MDX expression in the current context of the cube or query. The string expression can be dynamic as well as a fixed string. When the CONSTRAINED flag is omitted, the MDX expression can be arbitrarily complicated so long as it returns a single cell value. When the CONSTRAINED flag is provided, the string expression must contain only a constant value, or else an error will result. If the error occurs in evaluating a slicer or axis, the query execution will stop. If the error occurs in evaluating a result cell, the cell will have an error result.

See also: StrToSet, SetToStr (), TupleToStr (), Members (), .Properties (), .Name, .UniqueName

Subset (set, start [, count]) Returns: set Extension: AS2005, AS2000

This function returns up to count elements from set, starting at start. The start index is zero-based: the first element in the set is at index 0, and the last is at one less than the number of tuples in the set. If count is not specified or is greater than the number of elements in the set following Start, all elements from Start to the end of the set are returned. If count is less than 1, then an empty set is returned.

See also: Head (), Tail (), Index (), .Count, Count (), Rank ()

Sum (set [, numeric value expression]) Returns: number Standard

This function returns the sum of values found across all tuples in the set. If numeric value expression is supplied, then it is evaluated across set and its results are summed; otherwise, set is evaluated in the current context and the results are summed.

See also: Aggregate (), Avg (), Count (), .Count, Min (), Max ()

T

Tail (set [, count]) Returns: set Extension: AS2005, AS2000

This function returns a set of the last count elements from the given set. The order of elements in the given set is preserved. If count is omitted, the number of elements returned is 1. If count is less than 1, an empty set is returned. If the value of the count is greater than the number of tuples in the set, the original set is returned.

See also: Subset (), Head (),Index (), .Count, Count (), Rank ()

This Returns: subcube

This function returns the currently specified scope in an MDX script. May be assigned to or have properties set for it.

See also: .CurrentMember

ToggleDrillState (set1, set2 [, RECURSIVE]) Returns: set Standard

This function returns a set in which those members or tuples in set1 that are drilled up are drilled down and those members or tuples in set1 that are drilled down are drilled up. This function combines the operations of DrillUpMember () and DrillDownMember (). Set1 can contain tuples of arbitrary dimensionality; set2 must contain only members of one dimension. A member or tuple in set1 is considered drilled down if it has any descendant immediately following it and is considered drilled up otherwise. When a member is found without a descendant immediately after it, DrillDownMember () will be applied to it, with the RECURSIVE flag if the RECURSIVE is present.

See also: DrillDownMember (), DrillUpMember ()

TopCount (set, index [, numeric expression]) Returns: set Standard

BottomCount (set, index [, numeric expression]) Returns: set Standard

TopCount () returns the top index items found after sorting the set. The set is sorted on the numeric expression (if one is supplied). If there is no numeric expression, the cells found in the evaluation context are used. The BottomCount () function is similar to TopCount (), except that it returns the bottom index items. TopCount () returns elements ordered from largest to smallest in terms of the cells or expression used; BottomCount () returns them ordered from smallest to largest. Any duplicate tuples are retained during sorting, and those that make the cutoff are retained.

These functions always break the hierarchy. If members from multiple levels are combined in the set, then they are all treated as peers. If duplicate values exist for some of the cells in set, these functions may pick an arbitrary set. For example, suppose the set of values (when sorted) is as follows.

FRUIT	VALUE
Strawberries	12
Cantaloupes	10
Peaches	8
Apples	8
Kiwis	8
Bananas	4

In this case, selecting the top three or bottom two fruits based on values will cause an arbitrary choice to be made at the value of 8. The results are functionally equivalent to Head (Order (set, numeric value expression, BDESC), index) and Head (Order (set, numeric value expression, BASC), index).

Note that Essbase always removes tuples from the set whose numeric expression is NULL. If you want them included at some position, use CoalesceEmpty () in the numeric expression to convert the NULL to some value.

See also: TopSum (), BottomSum (), TopPercent (), BottomPercent ()

TopPercent (set, percentage, numeric expression) Returns: set Standard

BottomPercent (set, percentage, numeric expression) Returns: set Standard

TopPercent () returns the top percentage tuples of set, based on numeric expression if specified. The cells or expression are summed over the set, and the top set of elements whose cumulative total of the numeric expression is at least percentage is returned. Percentage is a numeric expression. For example, using the sorted set of fruits and values, TopPercent (fruit, 50, Value) will result in {Strawberries, Cantaloupes}. Strawberries is 24 percent of the total, Cantaloupes 1 Strawberries is 44 percent of the total, and Peaches would push the set over the 50 percent limit to 56 percent.

BottomPercent () behaves similarly, except that it returns the bottom set of elements whose cumulative total from the bottom is less than the specified percentage. TopPercent () returns elements ordered from largest to smallest in terms of the cells or expression used; BottomPercent () returns them ordered from smallest to largest.

The percentage is specified from 0 to 100 (not 0 to 1.0). These functions always break the hierarchy. Like TopCount () and BottomCount (), they may pick an arbitrary cutoff when some cells have the same values. Any duplicate tuples are retained during sorting, and those that make the cutoff are retained. Note that these functions do not have anything to do with taking tuples in the top or bottom percentile ranges according to the statistical definition of percentiles.

Note that Essbase always removes tuples from the set whose numeric expression is NULL. If you want them included at some position, use CoalesceEmpty () in the numeric expression to convert the NULL to some value.

See also: TopCount (), BottomCount (), TopSum (), BottomSum ()

TopSum (set, value, numeric expression) Returns: set Standard

BottomSum (set, value, numeric expression) Returns: set Standard

TopSum () returns the subset of set, after sorting it, such that the sum of the cells (or numeric value expression, if supplied) is at least value. This function always breaks the hierarchy. For example, given the sorted set of fruits and values, TopSum (fruit, 24, value) would return {Strawberries, Cantaloupes}. Strawberries' 12 is less than 24, and Strawberries 1 Cantaloupes is 22, while adding Peach's 8 to the 22 would push it over the limit of 24 to 30. The BottomSum () function behaves similarly, except that it returns the bottom set of elements whose cumulative total from the bottom is less than the specified value. TopSum () returns elements ordered from largest to smallest in terms of the cells or expression used; BottomSum () returns them ordered from smallest to largest.

These functions always break the hierarchy. Like TopCount () and BottomCount (), they may pick an arbitrary cutoff when some cells have the same values. Any duplicate tuples are retained during sorting, and those that make the cutoff are retained.

Note that Essbase always removes tuples from the set whose numeric expression is NULL. If you want them included at some position, use CoalesceEmpty () in the numeric expression to convert the NULL to some value.

See also: TopCount (), BottomCount (), TopPercent (), BottomPercent ()

TupleToStr (tuple) Returns: string Standard

This function constructs a string from a tuple. This will frequently be used to transfer a tuple specification to an external function. If the tuple contains only one dimension, the unique name for its member is placed in the string. (In this use, it is identical to Member.UniqueName.) If the tuple contains more than one dimension, Analysis Services constructs the string as follows. The string begins with an open parenthesis ["("] and ends with a closed parenthesis ["("]. In between the parentheses, the member's unique name is placed in the string for each dimension in the order they follow in the tuple. Each member is separated by a comma and a space. For example, the expression

     TupleToStr ( (Time.[1997], Customer.[AZ]) ) 

which uses names that are not quite the members' unique names, might return the following string:

     "([Time].[All Times].[1997], [Customer].[All     Customers].[Southwest].[AZ])" 

If the tuple is a result of an invalid member reference, then the resulting string is empty (instead of an error result).

The style of name generated depends on the MDX Unique Name Style and MDX Compatibility settings.

See also: SetToStr (), StrToTuple (), .Name, .UniqueName, StrToMember (), Members (), StrToValue (), LookupCube ()

U

Union (set1, set2 [, ALL]) Returns: set Standard

set1 + set2 Returns: set Extension: AS2005, AS2000

This function returns the union of the two sets. The ALL flag controls whether duplicates are retained or eliminated; by default, they are eliminated. When duplicates of each tuple are eliminated, the first instance of each tuple is retained according to the order in which it appears. The effect of this function is that set2 is appended to set1, and then all copies of each tuple are removed after the first instance of that tuple in the appended version. When duplicates are retained, any duplicates in the set1 are retained, and any additional copies in set2 are also retained. The effect of the union is that set2 is appended to set1. For example, the expression

     Union (       { [Customer].[AZ].[Phoenix], [Customer].[AZ].[Scottsdale],         [Customer].[KS].[Pittsburg], [Customer].[AZ].[Phoenix] },       { [Customer].[NM].[Albuquerque], [Customer].[AZ].[Phoenix],         [Customer].[AZ].[Scottsdale], [Customer].[AZ].[Phoenix]     ) 

yields the following set:

     { [Customer].[AZ].[Phoenix], [Customer].[AZ].[Scottsdale],     [Customer].[KS].[Pittsburg], [Customer].[NM].[Albuquerque] } 

The expression

     Union (       { [Customer].[AZ].[Phoenix], [Customer].[AZ].[Scottsdale],       [Customer].[KS].[Pittsburg], [Customer].[AZ].[Phoenix] },       { [Customer].[NM].[Albuquerque], [Customer].[AZ].[Phoenix],       [Customer].[AZ].[Scottsdale], [Customer].[AZ].[Phoenix] }       , ALL     ) 

yields the following set:

     { [Customer].[AZ].[Phoenix], [Customer].[AZ].[Scottsdale],       [Customer].[KS].[Pittsburg], [Customer].[AZ].[Phoenix],       [Customer].[NM].[Albuquerque], [Customer].[AZ].[Phoenix],       [Customer].[AZ].[Scottsdale], [Customer].[AZ].[Phoenix] } 

Microsoft OLAP Services and Analysis Services also provide + as an alternate way of specifying Union (). Duplicates are removed from the resulting set. The expression Set1 + Set2 + Set3 is equivalent to Union (Set1, Union (Set2, Set3)).

See also: Intersect (), Except (), {}

Dimension.UniqueName Returns: string Extension: AS2005, AS2000

This function returns the unique name of a dimension. In Microsoft's OLAP products, this does not include the name of the cube.

See also: .Name

Level.UniqueName Returns: string Extension: AS2005, AS2000

This function returns the unique name of a level. In Microsoft's OLAP products, this does not include the name of the cube. It will be either [Dimension].[Level] or [Dimension].[Hierarchy].[Level], depending on the structure of the dimension.

See also: .Name, Levels ()

Member.UniqueName Returns: string Extension: AS2005, AS2000

This function returns the unique name of a member. In Microsoft's OLAP products, this does not include the name of the cube, and the results are dependent on the MDX Unique Name Style connection property or the equivalent server-side setting.

See also: StrToMember (), StrToTuple (), StrToSet (),TupleToStr (), .Name

member.UnknownMember Returns: member Extension: AS2005

This function returns the member created by AS2005 for handling "unknown hierarchy" conditions in fact data. Unknown members can be created at one of the following levels:

• The top level, in attribute hierarchies that cannot be aggregated

• The level beneath the (All) level for natural hierarchies

• Any level (for other hierarchies)

If the unknown member is requested for a member, then the child of the given member that is an "unknown member" is returned. If the unknown member does not exist under the given member, a NULL member reference is returned.

See also: .CurrentMember, .DefaultMember

Unorder () Returns: set Extension: AS2005

This function relaxes MDX-specified ordering from the tuples or members of a set. Generally speaking, this function is a hint for optimization of a set operation. For example, a set that is input to NonEmpty (), TopCount () or Order () may not have any need to have the sequence of input tuples preserved. (For functions that sort the input sets, like TopCount () and Order (), this is because the stable sorting requirement becomes relaxed.) Therefore, NonEmpty (Unorder (set)) may run more quickly than NonEmpty (set). Note that AS2005 automatically attempts to perform this optimization for functions like Sum (), Aggregate (), so you may not notice any performance gain attempting to aggregated Unorder ()'d sets.

UserName Returns: string Extension: AS2005, AS2000

This function returns the username of the user executing the function. The name is returned in Domain\Name format. For example, if user Lisa in the domain ITCMAIN invokes a calculation that uses this function, it will return "ITCMAIN\Lisa."

See also: CustomData

V

ValidMeasure (tuple) Returns: tuple Extension: AS2005, AS2000

This function returns the value of the measure specified by the tuple where the measure has been projected to a meaningful intersection in a virtual cube. When a virtual cube joins two or more regular cubes that have different dimensionality, all base data values in the virtual cube are found at the ALL levels of each dimension that is not shared by all cubes. You can always reference these base data cells by explicitly qualifying the measure reference to the ALL level of each dimension (for example, ([Measures].[Employee Count], [Product].[All Products], [Customer].[All Customers])). This function is a convenience because you do not need to explicitly reference all of the dimensions that are not relevant to the measure.

The tuple may contain members from any dimensions of the virtual cube (and it does not need to have a measure in it). Any members for noncommon dimensions for the measure are projected to the ALL member. Any members for dimensions that are in common are used to locate the value returned. The function can be used with regular cubes, but in that case it does nothing to change the location of reference for the measure.

Note that you need an extra set of parentheses to define the tuple if it contains more than one member

     ValidMeasure ( ([Measures].[Qty Purchased], [Time].PrevMember) ) 

instead of

     ValidMeasure ( [Measures].[Qty Purchased], [Time].PrevMember ) 

In AS2005, keep in mind that setting the IgnoreUnrelatedDimensions option on a measure group will turn on an automatic ValidMeasure behavior, which obviates the need for this function

measure[.Value] Returns: number or string Standard (except AS2000)

The .Value operator returns the value of the specified measure at the location formed by the current members of all other dimensions in context. We show this operator as optional because it is the default operator on a measure in a calculation or query context. If you leave it off, you get the value of the measure anyway because the default interpretation of a measure is to take its value. This operator exists simply as a specific counterpart to the other functions that return aspects of a member, such as .Name (which would return the name of the measure).

Var (set [, numeric value expression]) Returns: number Standard

Variance (set [, numeric value expression]) Returns: number Extension: AS2005, AS2000

VarianceP (set [, numeric value expression]) Returns: number Extension: AS2005, AS2000

VarP (set [, numeric value expression]) Returns: number Extension: AS2005, AS2000

These functions return the variance of a numeric expression evaluated over a set. If the numeric expression is not supplied, these functions evaluate the set within the current context to determine the values to use. The formula for obtaining the variance is Var () calculates the sample variance and uses the unbiased population formula (dividing by n - 1), while VarP () calculates the population variance and uses the biased formula (dividing by n). Variance () and VarianceP () are aliases of Var () and VarP (), respectively.

See also: Stdev (), StdevP ()

VisualTotals (set, pattern) Returns set

Extension: AS2005, AS2000

The function accepts a set that can contain members at any level from within one dimension. (The set can only include members from one dimension.) Typically, the set contains members with some ancestor/ descendant relationship. For the set that is returned, aggregate data values for the ancestor data values are calculated as aggregates of the children or descendants provided in the set instead of using all children from the dimension. (When the set corresponds to children visible in the GUI, the parents are totaled according to the visible members, which is the origin of the "visual totals" name). The pattern is a string that is used to identify visual-total members "visually" totaled members are identified in the results using this pattern string. Wherever an asterisk appears in the string, the name (the simple name, not the unique name) of that parent member is inserted. A double asterisk (⊛⊛) causes an asterisk character to appear in the name.

While this function exists in both AS 2005 and AS 2000, its behavior has changed substantially between the releases. In AS2005, the function works with all measure aggregation types, in contrast with AS2000 in which it did not work with DISTINCT COUNT measures. We will describe the AS 2005 behavior first and then the AS 2000 behavior.

In Analysis Services 2005, VisualTotals () effectively redefines the parent members listed in the set to have only children/descendants as they appear in the set, changing the display caption of the parent members to match the naming pattern as well. This affects all uses of the members in the entire query, not just within the set. In terms of calculations, it is similar to, but not the same as defining a subcube consisting of just the lowest-level members in the set.

Consider the following query, whose results are shown in Figure A-38:

     WITH     SET [VT1] AS     VisualTotals (       { [Product].[ByCategory].[Category].&[2],         {[Product].[ByCategory].[Subcategory].&[12],          [Product].[ByCategory].[Subcategory].&[15]}       },       "(total *)"     )     SELECT     { [Measures].[Unit Sales], [Measures].[Dollar Sales]} on 0,     { [VT1],       [Product].[ByCategory].[Category].&[2],       [Product].[ByCategory].[Family].&[1] }     } on 1     FROM [Sales] 

	Unit Sales	Dollar Sales
(total Outdoor Gear)	13,505	$346,008.44
Inflatable Boats	4,625	$199,377.93
Multi-Tools, Knives	8,880	$226,631.05
(total Outdoor Gear)	13,505	$346,008.44
Outdoor & Sporting	256,691	$6,493,322.31

Figure A-38: Results of VisualTotals () in AS 2005.

You can see that the first three rows represent the VT1 set, which includes two product subcategories and their Outdoor Gear parent. The fourth row was a request for the Outdoor Gear member outside of the VisualTotals (), but it returns the same value as the VisualTotals () result since the member has been redefined for the whole query. The last row is a request for the Outdoor & Sporting member which is the parent of Outdoor Gear. Its aggregates include only the visible Outdoor Gear values, but also all the values from the siblings of Outdoor Gear. Unlike a subcube, VisualTotals () does not effectively make members invisible, but it does change the set of children/descendants that contribute to an ancestor.

This global impact has a slightly surprising effect. Consider the following query, whose results are shown in Figure A-39:

     WITH     SET [VT1] AS     VisualTotals (         { [Product].[ByCategory].[Category].&[2],         { [Product].[ByCategory].[Subcategory].&[12],           [Product].[ByCategory].[Subcategory].&[15] }         },         "(total *)"     )     SET [VT2] AS     VisualTotals (         { [Product].[ByCategory].[Category].&[2],         { [Product].[ByCategory].[Subcategory].&[9],           [Product].[ByCategory].[Subcategory].&[12] }         },         "(total *)"     )     SELECT     { [Measures].[Unit Sales], [Measures].[Dollar Sales]} on 0,     {  [VT1], [VT2] } on 1     FROM [Sales] 

	Unit Sales	Dollar Sales
(total Outdoor Gear)	14,536	$385,402.15
Inflatable Boats	4,625	$199,377.93
Multi-Tools, Knives	8,880	$226,631.05
(total Outdoor Gear)	14,536	$385,402.15
Coolers	9,911	$266,024.76
Inflatable Boats	4,625	$119,377.93

Figure A-39: Results of two VisualTotals () using same parent member.

You can see two different VisualTotals () calls, with the same parent member in each. This results in that member appearing twice in the query result. The aggregated values are calculated by the second call's set.

Note that placement of members is more flexible in AS2005 than in AS2000. In particular, you can have visual totals parents following their children instead of only preceding them, which enables more display requirements to be fulfilled.

In Analysis Services 2000, VisualTotals () returns a set that includes dynamically created calculated members that total up the given descendants for an ancestor. When a parent member is followed by one or more of its children in the given set, or an ancestor by one or more of its descendants, the function replaces that parent or ancestor member with a synthesized member that totals the values taken only from the children or descendants that follow it in the set. The name of the synthesized member is formed from the pattern given in the pattern argument. The order of the appearance of members is important; a parent that is to be replaced by a synthetic visual total must appear immediately before its children. The sets created by the DrillDownXXX functions are likely to fit VisualTotal ()'s member ordering requirements.

The synthesized members are named using the text from the pattern string, per the rules described earlier. Consider the following VisualTotals () expression, which contains numerous parents and ancestors (its results are shown in Figure A-40).

     WITH     MEMBER [Measures].[AvgPrice] AS '[Measures].[Total] /     [Measures].[Qty]', FORMAT_     SET [Rowset] AS 'VisualTotals (     {     [Time].[All Time].[2001].[Q1, 2001],     [Time].[All Time].[2001],     [Time].[All Time].[2001].[Q1, 2001].[January],     [Time].[All Time].[2001].[Q1, 2001].[February],     [Time].[All Time].[2001].[Q2, 2001],     [Time].[All Time].[2001].[Q2, 2001].[May],     [Time].[All Time].[2001].[Q2, 2001].[June],     [Time].[All Time].[2001].[Q1, 2001],     [Time].[All Time].[2001].[Q2, 2001],     [Time].[All Time],     [Time].[All Time].[2001].[Q1, 2001].[January].[Jan 01, 1998],     [Time].[All Time].[2001].[Q1, 2001].[January].[Jan 02, 1998]     }     , "vt *")'     SELECT     { {[Measures].[Qty],  [Measures].[Total],  [Measures].[AvgPrice} } on     axis (0),     { [Time].[All Time].[2001].[Quarter 1], [Rowset]     } on axis (1)     FROM cakes03 

	Qty	Total	Average Price
Quarter 1	1,811,965.00	44,166,000.00	24.37464
vt 2001	5,965,904.00	133,988,515.00	22.45905
January	620,829.00	16,343,870.00	26.32588
February	572,194.00	13,863,990.00	24.26447
vt Quarter 2	1,186,056.00	23,660,064.00	19.94852
May	614,945.00	12,267,870.00	19.94954
June	571,111.00	11,392,190.00	19.94743
Quarter 1	1,811,965.00	44,166,000.00	24.37464
Quarter 2	1,774,860.00	35,934,600.00	20.24644
vt All Time	39,239.00	1,220,641.20	31.10786
Jan 01, 2001	16,127.00	492,969.90	30.56799
Jan 01, 2001	23,112.00	727,671.30	31.48457

Figure A-40: Sample results from VisualTotals ().

This highlights some of the useful aspects of VisualTotals () and also some of its quirks, which you will need to be aware of. Looking at the [vt All Time] member toward the bottom of the report, the All Time total is simply the sum of the two day-level members following it, and a similar look at [vt Q2, 2001] shows that it is the sum of the two Q2 months following it. Looking at the Qty measure for [vt 2001], the value 1,186,056 is the sum of values found for January, February, [vt Quarter 2], [Quarter 1], and [Quarter 2]. In other words, [vt Quarter 2] was not double-counted with [May] and [June]. You do need to be careful in how you place descendants, however. [Quarter 1] and [Quarter 2] are included in the total without regard to the fact that their descendants have already been incorporated into the total.

The bottom three rows of the VisualTotals () expression just presented show that VisualTotals () can work against ancestors and descendants of arbitrary depth. The [All Time] member is the higher level member in the dimension, while each day is at the leaf level. If you observe the values for the [AvgPrice] measure in the query, you can see that it is calculated after the visual totals, despite the fact that it is at solve order precedence 0. The VisualTotals () aggregation is documented to be at solve order -4096, so calculated member definitions will ordinarily override VisualTotals () synthetic aggregates. Meanwhile, VisualTotals () synthetic aggregates should be calculated from the results of custom rollups, because they are at solve order -5119.

Note that the synthetic members in the set returned by VisualTotals () are almost fully equivalent to calculated members created by other means. They cannot exist outside of a set as a calculated member, so they will not appear as metadata items through OLE DB for OLAP. They can be part of a set held in a CREATE SET statement and are treated as another calculated member by StripCalculatedMembers (). They can be filtered by name and unique name. They cannot, however, be referenced by a tuple reference in a formula because they are not entered into Microsoft Analysis Services's internal list of metadata objects.

W

WTD ([member]) Returns: set Standard

WTD () is the equivalent of PeriodsToDate () with the level set to Week. If member is not specified, it defaults to the current member of the Time-typed dimension. If no Time-typed dimension exists in the cube, or if it does not have a level tagged as Week, then a parser error results.

See also: PeriodsToDate (), QTD (), MTD (), YTD ()

X

expr1 XOR expr2 Returns: Boolean Standard

The result of this operator is true if only either expr1 or expr2 is true, and false if they both are true or both false. Both expressions must be evaluated in order to determine this.

Y

YTD ([member]) Returns: set Standard

YTD () is the equivalent of PeriodsToDate () with the level set to Year. If member is not specified, it defaults to the current member of the Time-typed dimension. If no Time-typed dimension exists in the cube, or if it does not have a level tagged as Year, then a parser error results.

See also: PeriodsToDate (), QTD (), MTD (), WTD ()