These tables have example expressions that can be used as models when you are setting up expressions for dynamic user lists.
Note: These same types of expressions can be used when creating expression filters on levels in views. See Example Expressions for Filtering Levels.
For each type of user list expression that follows, a specific example for the Product level is provided followed by the generic MDX format for that type of user list.
|
Returns Members that are... |
Example Expression and MDX |
|
Equal to X |
[Product].[Product].CurrentMember IS [Product].[Product].[Product].[954023] Returns the Product member with a value equal to 954023 Generic MDX Format [DimensionName].[HierarchyName].CurrentMember IS [DimensionName].[HierarchyName].[LevelName].[value] |
|
Not equal to X |
[Product].[Product].CurrentMember.Name <> "954023" Returns Product members with values not equal to 954023 Generic MDX Format [DimensionName].[HierarchyName].CurrentMember.Name <> "value" |
|
Greater than X |
[Product].[Product].CurrentMember.Name > "954023" Returns Product members with values greater than 954023 Generic MDX Format [DimensionName].[HierarchyName].CurrentMember.Name > "value" |
|
Greater than or equal to X |
[Product].[Product].CurrentMember.Name >= "954023" Returns Product members with values greater than or equal to 954023 Generic MDX Format [DimensionName].[HierarchyName].CurrentMember.Name >= "value" |
|
Less than X
|
[Product].[Product].CurrentMember.Name < "954023" Returns Product members with values less than 954023 Generic MDX Format [DimensionName].[HierarchyName].CurrentMember.Name < "value" |
|
Less than or equal to X |
[Product].[Product].CurrentMember.Name <= "954023" Returns Product members with values less than or equal to 954023 Generic MDX Format [DimensionName].[HierarchyName].CurrentMember.Name <= "value" |
|
In the specified range |
[Product].[Product].CurrentMember.Name >= "954023" AND [Product].[Product].CurrentMember.Name <= "954622" Returns Product members with values greater than or equal to 954023 and less than or equal to 954622 Generic MDX Format [DimensionName].[HierarchyName].CurrentMember.Name >= "value" AND [DimensionName].[HierarchyName].CurrentMember.Name <= "value" |
|
Equal to X OR equal to Y |
[Product].[Product].CurrentMember IS [Product].[Product].[Product].[954023] OR [Product].[Product].CurrentMember IS [Product].[Product].[Product].[954622] Returns Product members with values equal to 954023 or 954622 Generic MDX Format [DimensionName].[HierarchyName].CurrentMember IS [DimensionName].[HierarchyName].[LevelName].[value 1] OR [DimensionName].[HierarchyName].CurrentMember IS [DimensionName].[HierarchyName].[LevelName].[value 2] |
For each type of user list expression that follows, a specific example for the Customer ShipTo level is provided followed by the generic MDX format for that type of user list.
|
Return Members with |
Example Expression and MDX |
|
Are equal to the specified text |
[Customer Ship To].[Customer Ship To].CurrentMember.Properties("State") = "IL" Returns Customer ShipTo members with State equal to “IL” Generic MDX Format [DimensionName].[HierarchyName].CurrentMember.Properties("AttributeRelationshipName") = "value" |
|
Contain the specified text |
(INSTR(1, [Customer ShipTo].[Customer ShipTo].CurrentMember.Properties("ShpTo Postal Code"), "8121") <> 0) Returns Customer ShipTo members with Postal Codes that contain the text “8121" Generic MDX Format (INSTR(1, [DimensionName].[HierarchyName].CurrentMember.Properties("AttributeRelationshipName"), "value") <> 0) |
|
Don't contain the specified text |
(INSTR(1, [Customer ShipTo].[Customer ShipTo].CurrentMember.Properties("ShpTo Postal Code"), "2") = 0) Returns Customer ShipTo members with Postal Codes that don't contain the text “2” Generic MDX Format (INSTR(1, [DimensionName].[HierarchyName].CurrentMember.Properties("AttributeRelationshipName"), "value") = 0 |
|
Begin with the specified text |
LEFT([Customer ShipTo].[Customer ShipTo].CurrentMember.Properties("ShpTo Postal Code"), 3) = "481" Returns Customer ShipTo members with Postal Codes that start with the 3 characters “481” Generic MDX Format LEFT([DimensionName].[HierarchyName].CurrentMember.Properties("AttributeRelationshipName"), #) = "value" The # in the generic format is the length of the string of characters for the specified value text. For example, if the value was "4812" rather than "481" in the Customer ShipTo example, then the # would have been 4 rather than 3 in that expression. |