MDX Member Properties - User-Defined Member Properties
APPLIES TO: SQL Server Analysis Services Azure Analysis Services Power BI Premium
User-defined member properties can be added to a specific named level in a dimension as attribute relationships. User-defined member properties cannot be added to the (All) level of a hierarchy, or to the hierarchy itself.
Creating User-Defined Member Properties
User-defined member properties can be added to server-based dimensions or cubes either through the user interface or programmatically:
To add user-defined member properties through the user interface, you use Dimension Designer in SQL Server Data Tools (SSDT). For more information, see Define Attribute Relationships.
To add user-defined member properties programmatically, your application can use either Analysis Manager Objects (AMO) or a combination of XML for Analysis (XMLA) and Analysis Services Scripting Language (ASSL). For more information, see Attribute Relationships.
Retrieving User-Defined Member Properties
You can retrieve user-defined member properties using either the PROPERTIES keyword or the Properties function.
Using the PROPERTIES Keyword to Retrieve User-Defined Member Properties
The syntax that retrieves user-defined member properties is similar to that used to retrieve intrinsic level member properties, as shown in the following syntax:
DIMENSION PROPERTIES [Dimension.]Level.<Custom_Member_Property>
The PROPERTIES keyword appears after the set expression of the axis specification. For example, the following MDX query the PROPERTIES keyword retrieves the
List Price and
Dealer Price user-defined member properties and appears after the set expression that identifies the products sold in January:
SELECT CROSSJOIN([Ship Date].[Calendar].[Calendar Year].Members, [Measures].[Sales Amount]) ON COLUMNS, NON EMPTY Product.Product.MEMBERS DIMENSION PROPERTIES Product.Product.[List Price], Product.Product.[Dealer Price] ON ROWS FROM [Adventure Works] WHERE ([Date].[Month of Year].[January])
Using the Properties Function to Retrieve User-Defined Member Properties
Alternatively, you can access custom member properties with the Properties function. For example, the following MDX query uses the WITH keyword to create a calculated member consisting of the
List Price member property:
WITH MEMBER [Measures].[Product List Price] AS [Product].[Product].CurrentMember.Properties("List Price") SELECT [Measures].[Product List Price] on COLUMNS, [Product].[Product].MEMBERS ON Rows FROM [Adventure Works]
For more information about building calculated members, see Building Calculated Members in MDX (MDX).