MDX - Intrinsic Members
Posted: Fri May 27, 2022 5:22 pm
Intrinsic members are basically built-in properties that can be used in MDX for selection and filtering.
The below table lists a few of these with some examples based on the SData samples.
Types are Context Sensitive (CS) or Non Context Sensitive (NCS) - See this article for an explanation.
The list below includes some other items that still need to be researched and may be relevant using the REST API for MDX and Cellsets:
MEMBER_UNIQUE_NAME, PARENT_UNIQUE_NAME, DIMENSION_UNIQUE_NAME, LEVEL_UNIQUE_NAME
DIMENSION_CARDINALITY, DIMENSION_NAME, DIMENSION_ORDINAL, DIMENSION_CAPTION, DIMENSION_TYPE
HIERARCHY_CARDINALITY, HIERARCHY_NAME, HIERARCHY_UNIQUE_NAME, HIERARCHY_CAPTION
LEVEL_TYPE, LEVEL_CARDINALITY
IS_PLACEHOLDER, CATALOG_NAME, CUBE_NAME, STRUCTURE
TM1UPDATEABLE, , UPDATEABLE, READONLY_STATUS, TM1RULEDERIVED, ANNOTATED, CONSOLIDATED,
LAST_SCHEMA_UPDATE, LAST_DATA_UPDATE, CELL_ORDINAL
FORMATTED_VALUE, FORMAT_STRING, TM1FORMAT_STRING
Let me know should there be some functions missed, errors that need correction or better examples if you have them.
The below table lists a few of these with some examples based on the SData samples.
Types are Context Sensitive (CS) or Non Context Sensitive (NCS) - See this article for an explanation.
Property | Type | Example | Notes |
ELEMENT_INDEX | NCS | FILTER( {TM1SUBSETALL( [model] )}, StrToValue([model].CurrentMember.Properties("ELEMENT_INDEX"))<=10) | Filtering based on the element index per the properties window in the subset editor |
ELEMENT_LEVEL | NCS | FILTER( {TM1SUBSETALL( [model] )}, [model].CurrentMember.Properties("ELEMENT_LEVEL")="0") | Based on Level 0 being N-Level or Leaf and counting up as you ascend the hierarchy. |
ELEMENT_TYPE | CS | FILTER(TM1SubsetAll([account1]), [account1].currentmember.properties("ELEMENT_TYPE")="2") | Will find all String Type elements in the dimension. 1=N, 2=S, 3=C |
MEMBER_CAPTION | CS | FILTER( {TM1SUBSETALL( [model] )}, INSTR(1, [model].CurrentMember.PROPERTIES("MEMBER_CAPTION"), "Sedan", 1)>0) | Does not appear to be looking for the Caption attribute/alias |
MEMBER_NAME | NCS | FILTER ({TM1SUBSETALL( [model] )}, [model].CurrentMember.Properties("MEMBER_NAME")="T Series 2.8 L Sedan") | Appears to be the same as: FILTER ({TM1SUBSETALL( [model] )}, [model].CurrentMember.Name="T Series 2.8 L Sedan") |
MEMBER_ORDINAL | CS | FILTER( ORDER ({TM1SUBSETALL( [model] )}, StrToValue([model].CurrentMember.Properties("MEMBER_ORDINAL")),BASC), StrToValue([model].CurrentMember.Properties("MEMBER_ORDINAL"))<=10) | Ordinal appears to be the index number of the member when sorted by Hierarchy. The example returns the first 10 members in the hierarchy (rollup) |
MEMBER_WEIGHT | CS | FILTER(TM1SubsetAll([account1]), [account1].currentmember.properties("MEMBER_WEIGHT")<>"1.000000") or FILTER(TM1DrillDownMember(TM1SubsetAll([test]),ALL,Recursive), StrToValue([test].currentmember.properties("MEMBER_WEIGHT"))=-1) | Find elements based on their weighting |
LEVEL_NUMBER | NCS | FILTER( {TM1SUBSETALL( [model] )}, [model].CurrentMember.Properties("LEVEL_NUMBER")="0") | Based on Level 0 being Root and counting up for subsequent levels below Root Members Same as: FILTER( {TM1SUBSETALL( [model] )}, [model].CurrentMember.Level.Ordinal=0) |
LEVEL_NAME | NCS | FILTER( {TM1SUBSETALL( [model] )}, [model].CurrentMember.Level.Name="Series") | Filter on named levels per the }HierarchyProperties cube |
PARENT_LEVEL | CS | FILTER( {TM1SUBSETALL( [model] )}, [model].CurrentMember.Properties("PARENT_LEVEL")>"0") | Returns all Members without Root entries which would be Parent's with a Level of 0 |
CHILDREN_CARDINALITY | CS | FILTER( FILTER(TM1DrillDownMember(TM1SubsetAll([test]),ALL,Recursive), [test].currentmember.properties("CHILDREN_CARDINALITY")="0"), [test].currentmember.properties("ELEMENT_TYPE")="3")) | CHILDREN_CARDINALITY = Number of children for a member Example returns C Level Members having exactly No Child elements. Filtering on "0" returns Leaf members or C Level members without any children. Below will return N and C with no children: FILTER( {TM1SUBSETALL( [model] )}, Count([model].CurrentMember.Children)=0) |
DEFAULT_MEMBER | NCS | {[Model].DefaultMember} | As defined in the }HierarchyProperties cube |
ALL_MEMBER | NCS | {[Model].AllMembers} | Seems same as {[Model].Members} |
The list below includes some other items that still need to be researched and may be relevant using the REST API for MDX and Cellsets:
MEMBER_UNIQUE_NAME, PARENT_UNIQUE_NAME, DIMENSION_UNIQUE_NAME, LEVEL_UNIQUE_NAME
DIMENSION_CARDINALITY, DIMENSION_NAME, DIMENSION_ORDINAL, DIMENSION_CAPTION, DIMENSION_TYPE
HIERARCHY_CARDINALITY, HIERARCHY_NAME, HIERARCHY_UNIQUE_NAME, HIERARCHY_CAPTION
LEVEL_TYPE, LEVEL_CARDINALITY
IS_PLACEHOLDER, CATALOG_NAME, CUBE_NAME, STRUCTURE
TM1UPDATEABLE, , UPDATEABLE, READONLY_STATUS, TM1RULEDERIVED, ANNOTATED, CONSOLIDATED,
LAST_SCHEMA_UPDATE, LAST_DATA_UPDATE, CELL_ORDINAL
FORMATTED_VALUE, FORMAT_STRING, TM1FORMAT_STRING
Let me know should there be some functions missed, errors that need correction or better examples if you have them.