Example Dynamic User List Expressions

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.

More: Click here for a complete list of all example MDX expressions that exist in this online help for all types of Stratum.Viewer items (calculated measure items, measure item captions, filters, and more).

Members in Expression

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]

Attribute Relationships in Expression

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
Attribute Relationships that...

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.