Professional Microsoft® SQL Server® Analysis Services 2008 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 members and default measures, or 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 Customer Geography hierarchy 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 to customers of different countries with sales of customers in Australia.

  Such an expression is typically used in a 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+"]")), POST).ITEM(0).ITEM(0), ...