MDX Property Functions

Property functions in Multidimensional Expressions (MDX) are used to retrieve specific properties of dimensions, members, and other MDX objects. These functions are crucial for understanding the structure and characteristics of your cube data.

Overview

Property functions allow you to access metadata associated with MDX objects. This can include names, levels, parents, children, unique names, and various other attributes that define the object's context within the cube.

Common Property Functions

`Name()`

Returns the name of the specified member or set.

Name( [Dimension.Member] )

Example:

SELECT {[Measures].[Internet Sales Amount]} ON COLUMNS,
  [Date].[Calendar].Members ON ROWS
FROM [Adventure Works]
WHERE ( Name([Date].[Calendar].&[2004]) )

`LevelName()`

Returns the name of the level that a member belongs to.

LevelName( [Dimension.Member] )

Example:

SELECT {[Measures].[Internet Sales Amount]} ON COLUMNS,
  [Product].[Category].Members ON ROWS
FROM [Adventure Works]
WHERE ( LevelName([Product].[Category].[Bikes]) )

`Parent()`

Returns the parent member of the specified member.

Parent( [Dimension.Member] )

Example:

SELECT {[Measures].[Internet Sales Amount]} ON COLUMNS,
  [Geography].[State-Province].Children ON ROWS
FROM [Adventure Works]
WHERE ( Parent([Geography].[State-Province].[California]) )

`UniqueName()`

Returns the unique name of a member.

UniqueName( [Dimension.Member] )

Example:

SELECT {[Measures].[Internet Sales Amount]} ON COLUMNS,
  [Date].[Calendar].Members ON ROWS
FROM [Adventure Works]
WHERE ( UniqueName([Date].[Calendar].&[2004]) )

`Properties()`

Returns a specified property of a member. This is a versatile function that can retrieve various custom or standard properties.

Properties( [Dimension.Member], PropertyName )

Example (assuming a 'Color' property exists for Product members):

SELECT {[Measures].[Internet Sales Amount]} ON COLUMNS,
  [Product].[Product].Members ON ROWS
FROM [Adventure Works]
WHERE ( Properties([Product].[Product].[Bicycle Black 20], "Color") )

Using Property Functions

Property functions are often used in conjunction with other MDX functions to build complex queries. For instance, you might use LevelName() to check if a member is at a specific level before applying a calculation, or use Parent() to navigate up the hierarchy.

Note: The availability and names of properties can vary depending on the cube schema and how it was designed. Always consult your cube's metadata or your database administrator for specific property names.

More Property Functions

Other notable property functions include:

Properties(member_expression, property_name [, property_name] ... ) - Retrieves one or more properties of a member.
MemberProperties(member_expression) - Returns a set of members that represent the properties of the specified member.
IsEmpty(expression) - Returns TRUE if the expression evaluates to an empty value.

For a comprehensive list and detailed syntax of each property function, please refer to the official MDX function reference.