Using Functions in Calculated Members

Using Functions in Calculated Members

Calculated members in Multidimensional Expressions (MDX) are extremely flexible. One of the ways in which calculated members provide such flexibility is in the wide variety of functions available for use in MDX. Besides the intrinsic MDX functions provided by the Microsoft® SQL Server™ 2000 Analysis Services function library, calculated members can also take advantage of external function libraries to supply additional capability.

A discussion of all of the myriad ways to use calculated members is beyond the scope of this topic. Instead, this topic focuses on the most commonly employed operators and functions in calculated members, and how to use them.

Operators

MDX supports a variety of arithmetic, logical, and comparison operators for use in MDX expressions.

Arithmetic Operators

Arithmetic operators support a basic set of arithmetic operations. Arithmetic precedence is followed when resolving arithmetic operations; multiplication and division operators are processed first, followed by addition and subtraction operators. If all of the arithmetic operators used in an expression have the same order of precedence; for example, as in the statement a + b + c + d, the arithmetic operators are handled in a left to right order. The basic arithmetic operators supported are specified in the following table.

Comparison Operators

Comparison operators compare two string, numeric or date expressions and return TRUE or FALSE based on the outcome of the tested comparison. For the purposes of comparison, null values are treated as zero when a null value is compared with a nonnull value. To check for null values in a cell, use the IsEmpty or Is functions to return TRUE if the cell contains a null value, FALSE otherwise. The TRUE and FALSE constants are supported; the TRUE constant evaluates to 1, while the FALSE constant evaluates to 0.

Bitwise Operators

Bitwise operators return a TRUE or FALSE value based on the review of logical expressions. As the TRUE and FALSE constants are supported, either of the following MDX expressions is now valid:

([Measures].[IsTrue] AND [Measures].[IsFalse]) = 0 
([Measures].[IsTrue] AND [Measures].[IsFalse]) = FALSE

Logical operators require expressions that can be evaluated to a logical value. Numeric expressions are implicitly converted to logical values before a logical comparison is performed. Any numeric expression that evaluates to 0 or NULL is considered FALSE, while any numeric expression that evaluates to something other than 0 is considered TRUE. String expressions are not implicitly converted; attempting to use a bitwise operator with string expressions will result in an error.

Set Operators

Set operators are provided to deal with the creation, separation, and joining of sets, as described in the following table.

Functions

MDX supplies a wide variety of functions for use in MDX expressions. This topic briefly touches on each category of functions, broken out by the type of data returned by the MDX functions in a specific category.

For more information about the categories of MDX functions, see MDX Function List.

Numeric Functions

MDX supplies a rich set of numeric functions, which can be used to perform a variety of aggregation and statistical calculations.

Aggregate functions in MDX are used to quickly perform a calculation across a number of members, usually specified as a set. For example, the Aggregate function aggregates the cells formed by all the members in a set, and can do so much easier than attempting to perform a manual aggregation. The Aggregate function is extremely powerful when combined with a measure that produces a sum, as the following MDX query example demonstrates:

WITH
   MEMBER [Time].[1st Half Sales] AS 'Aggregate({Time.[Q1], Time.[Q2]})'
   MEMBER [Time].[2nd Half Sales] AS 'Aggregate({Time.[Q3], Time.[Q4]})',
   MEMBER [Time].[Difference] AS 'Time.[2nd Half Sales] - Time.[1st Half Sales]',
SELECT
   { [Store].[Store State].Members} ON COLUMNS,
   { Time.[1st Half Sales], Time.[2nd Half Sales], Time.Difference} ON ROWS
FROM Sales
WHERE [Measures].[Store Sales]

The query produces the sum of the store sales for each state, with aggregations for the first and second halves of the year supplied by the first two calculated members using the Aggregate function, with a difference between the two supplied by a third calculated member.

MDX also supplies a list of statistical functions as well, for handling routine statistical calculations such as statistical covariance and standard deviation. For example, the Median function computes the median value across a set, as demonstrated in the following MDX query.

WITH
   MEMBER [Time].[1st Half Sales] AS 'Sum({[Time].[Q1], [Time].[Q2]})'
   MEMBER [Time].[2nd Half Sales] AS 'Sum({[Time].[Q3], [Time].[Q4]})'
   MEMBER [Time].[Median] AS 'Median(Time.Members)'
SELECT
   NON EMPTY { [Store].[Store Name].Members} ON COLUMNS,
   { [Time].[1st Half Sales], [Time].[2nd Half Sales], [Time].[Median]} ON ROWS
FROM Sales
WHERE [Measures].[Store Sales]

In this case, the [Time].[Median] calculated member provides the median value of store sales for each store, in addition to the aggregation of store sales for each half of the year for each store provided by the [Time].[1st Half Sales] and [Time].[2nd Half Sales] calculated members.

String Functions

MDX supplies a number of string functions not just for string processing within MDX expressions, but to support user-defined functions in MDX as well. For example, the MemberToStr function converts a member reference to a string in the MDX format for use with a user-defined function, as user-defined functions cannot accept object references from MDX.

Set Functions

Set functions are used to return sets in MDX, giving you the capability to easily build dynamically defined sets and quickly create reusable named sets. One of the most commonly used set functions, the Members function, returns all members, excluding calculated members, of a level or dimension as a set. The following MDX query example shows the Members function in action.

SELECT
   NON EMPTY { [Store].[Store Name].Members} ON COLUMNS,
   {Measures.[Store Sales]} ON ROWS
FROM Sales

The MDX query example returns the total store sales figures for each store in the Sales cube. Without the Members function, you would have to explicitly enter each and every store name for it to function as it does in the MDX query example.

Tuple Functions

As with set functions, tuple functions are used to return tuples in MDX. Tuple functions are also supplied, such as the StrToTuple function, to aid user-defined functions in MDX. As user-defined functions cannot handle MDX object references, a user-defined function can pass back a string return value in MDX format, representing a tuple, and use the StrToTuple function to convert it to a valid tuple reference.

Member Functions

Members are often referred to in calculated members; member functions allow calculated members to perform complex member retrieval, negotiating hierarchies and sets with equal ease.

The resolution of calculated members in MDX can be iterative in nature, as calculated members can be constructed based upon iteration over the members of a set. Functions in MDX such as CurrentMember allow you to take advantage of this iterative capability.

Other Functions

MDX supplies other functions as well, including functions that deal with dimensions, hierarchies, levels, and arrays. For example, the SetToArray function allows user-defined functions to receive set references as a variant array of individual members represented as strings, allowing you to create user-defined functions that can supply set related functionality.