Professional SQL Server™ Analysis Services 2005 with MDX

3.4. MDX Expressions

MDX expressions are partial MDX statements that evaluate to a value. They are typically used in calculations or in defining values for objects such as default member and default measure, or used for defining security expressions to allow or deny access. MDX expressions typically take a member, a tuple, or a set as a parameter and return a value. If the result of the MDX expression evaluation is no value, a Null value is returned. Following are some examples of MDX expressions.

Example 1:

Customer.[Customer Geography].DEFAULTMEMBER

This example returns the default member specified for the hierarchy Customer Geography of the Customer dimension.

Example 2:

(Customer.[Customer Geography].CURRENTMEMBER, Measures.[Sales Amount]) −
(Customer.[Customer Geography].Australia, Measures.[Sales Amount)

This MDX expression is used to compare the sales between customers of different countries with sales of customers in Australia.

Such an expression is typically used in a calculation called the calculated measure. Complex MDX expressions can include various operators in the MDX language along with the combination of the functions available in MDX. One such example is shown in Example 3.

Example 3:

COUNT(INTERSECT( DESCENDANTS( IIF( HIERARCHIZE(EXISTS[Employee].[Employee].MEMBERS, STRTOMEMBER("[Employee].[login].[login].&["+USERNAME+"]")), POST).ITEM(0).ITEM(0).PARENT.DATAMEMBER is HIERARCHIZE(EXISTS([Employee].[Employee].MEMBERS, STRTOMEMBER("[Employee].[login].[login].&["+USERNAME+"]")), ...