Non-Context Sensitive Member Properties
All members support a list of intrinsic member properties that are the same regardless of context. These properties provide additional information that can be used by applications to enhance the user's experience.
The following table lists the non-context sensitive intrinsic properties supported by SQL Server Analysis Services.
.gif) Note: |
|---|
|
Columns in the MEMBERS schema rowset support the intrinsic member properties listed in the following table. For more information about the MEMBERS schema rowset, see MDSCHEMA_MEMBERS Rowset. |
|
Property
|
Description
|
| ACTION_TYPE | A bitmask that indicates the types of actions on the member. This property can have one of the following values: -
MDACTION_TYPE_URL
-
MDACTION_TYPE_HTML
-
MDACTION_TYPE_STATEMENT
-
MDACTION_TYPE_DATASET
-
MDACTION_TYPE_ROWSET
-
MDACTION_TYPE_COMMANDLINE
-
MDACTION_TYPE_PROPRIETARY
-
MDACTION_TYPE_REPORT
-
MDACTION_TYPE_DRILLTHROUGH
|
| CAPTION | Same as MEMBER_CAPTION. |
| CHILDREN_CARDINALITY | The number of children that the member has. This can be an estimate, so you should not rely on this to be the exact count. Providers should return the best estimate possible. |
| CUBE_NAME | The name of the cube to which this member belongs. |
| CUSTOM_ROLLUP | The custom member expression. |
| CUSTOM_ROLLUP_PROPERTIES | The custom member properties. |
| DESCRIPTION | A human-readable description of the member. |
| DIMENSION_UNIQUE_NAME | The unique name of the dimension to which this member belongs. For providers that generate unique names by qualification, each component of this name is delimited. |
| HIERARCHY_UNIQUE_NAME | The unique name of the hierarchy. If the member belongs to more than one hierarchy, there is one row for each hierarchy to which the member belongs. For providers that generate unique names by qualification, each component of this name is delimited. |
| IS_DATAMEMBER | A Boolean that indicates whether the member is a data member. |
| KEYx | The key for the member, where x is the zero-based ordinal of the key. KEY0 is available for composite and non-composite keys, and KEY1,KEY2, etc are only available for composite keys. Key0, Key1,Key2, etc collectively form the composite key. |
| LCID x | The translation of the member caption in the locale ID hexadecimal value, where x is the locale ID decimal value (for example, LCID1009 as English-Canada). This is only available if the translation has the caption column bound to the data source. |
| LEVEL_NUMBER | The distance of the member from the root of the hierarchy. The root level is zero. |
| LEVEL_UNIQUE_NAME | The unique name of the level to which the member belongs. For providers that generate unique names by qualification, each component of this name is delimited. |
| MEMBER_CAPTION | A label or caption associated with the member. The caption is primarily for display purposes. If a caption does not exist, the query returns MEMBER_NAME. |
| MEMBER_KEY | The value of the member key in the original data type. MEMBER_KEY is for backward-compatibility. MEMBER_KEY has the same value as KEY0 for non-composite keys, and MEMBER_KEY property is null for composite keys. |
| MEMBER_NAME | The name of the member. |
| MEMBER_TYPE | The type of the member. This property can have one of the following values: -
MDMEMBER_TYPE_REGULAR
-
MDMEMBER_TYPE_ALL
-
MDMEMBER_TYPE_FORMULA
-
MDMEMBER_TYPE_MEASURE
-
MDMEMBER_TYPE_UNKNOWN
MDMEMBER_TYPE_FORMULA takes precedence over MDMEMBER_TYPE_MEASURE. Therefore, if there is a formula (calculated) member on the Measures dimension, the MEMBER_TYPE property for the calculated member is MDMEMBER_TYPE_FORMULA. |
| MEMBER_UNIQUE_NAME | The unique name of the member. For providers that generate unique names by qualification, each component of this name is delimited. |
| MEMBER_VALUE | The value of the member in the original type. |
| PARENT_COUNT | The number of parents that this member has. |
| PARENT_LEVEL | The distance of the member's parent from the root level of the hierarchy. The root level is zero. |
| PARENT_UNIQUE_NAME | The unique name of the member's parent. NULL is returned for any members at the root level. For providers that generate unique names by qualification, each component of this name is delimited. |
| SKIPPED_LEVELS | The number of skipped levels for the member. |
| UNARY_OPERATOR | The unary operator for the member. |
PROPERTIES Syntax for Non-Context Sensitive Properties
Use the following syntax to specify an intrinsic, non-context sensitive member property using the PROPERTIES keyword:
DIMENSION PROPERTIES Property
Notice that this syntax does not allow the property to be qualified by a dimension or level. The property cannot be qualified because an intrinsic member property that is not context sensitive applies to all members of an axis.
For example, an MDX statement that specifies the DESCRIPTION intrinsic member property would have the following syntax:
DIMENSION PROPERTIES DESCRIPTION
This statement returns the description of each member in the axis dimension. If you tried to qualify the property with a dimension or level, as in Dimension.DESCRIPTION or Level.DESCRIPTION, the statement would not validate.
Example
The following example returns the count of the resellers whose sales have declined over the previous time period, based on user-selected State-Province member values evaluated using the Aggregate function. The Hierarchize (MDX) and DrilldownLevel (MDX) functions are used to return values for declining sales for product categories in the Product dimension. The Properties (MDX) function is used to return the unique name of parent members returned on the column axis.
WITH MEMBER Measures.[Declining Reseller Sales] AS
Count
(Filter
(Existing
(Reseller.Reseller.Reseller)
, [Measures].[Reseller Sales Amount] <
(
[Measures].[Reseller Sales Amount]
,[Date].Calendar.PrevMember
)
)
)
MEMBER [Geography].[State-Province].x AS
Aggregate
(
{[Geography].[State-Province].&[WA]&[US]
, [Geography].[State-Province].&[OR]&[US]
}
)
SELECT NON EMPTY HIERARCHIZE
(AddCalculatedMembers
(
{DrillDownLevel
({[Product].[All Products]})
}
)
) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS
FROM [Adventure Works]
WHERE
(
[Geography].[State-Province].x
, [Date].[Calendar].[Calendar Quarter].&[2003]&[4]
,[Measures].[Declining Reseller Sales]
)The following example returns the translated caption for the product category member in the Product dimension in the Adventure Works cube for specified locales.
WITH
MEMBER Measures.CategoryCaption AS Product.Category.CurrentMember.MEMBER_CAPTION
MEMBER Measures.SpanishCategoryCaption AS Product.Category.CurrentMember.Properties("LCID3082")
MEMBER Measures.FrenchCategoryCaption AS Product.Category.CurrentMember.Properties("LCID1036")
SELECT
{ Measures.CategoryCaption, Measures.SpanishCategoryCaption, Measures.FrenchCategoryCaption } ON 0
,[Product].[Category].MEMBERS ON 1
FROM [Adventure Works]