Querying on Property Values

The need to query on property values frequently arises in practical situations. The Products dimension has a list of various kinds of apparel that are sold at a retail store chain. Suppose that the [Product Name] level of the Products dimension had three properties associated with it: Color, Type, and [Age Group].

To find the sales for children's red T-shirts (=Type), use the following MDX statement:

SELECT [Measures].[Sales] ON COLUMNS,
   Filter(Product.MEMBERS, ([Product Name].[Color] = "Red" AND
                            [Product Name].[Age Group] = "Children" AND
                            [Product Name].[Type] = "Tshirt")) ON ROWS
FROM ...

The syntax for referencing properties is similar to specifying a member of a level, for example:

   [<Level>].[<PropertyName>] or [<Member>].[<PropertyName>]

Occasionally, this syntax results in ambiguity or conflict with an existing member name or keyword (for example, "Children"). To resolve this, the following syntax is also supported:

   [<Level>].Properties("<PropertyName>")

The following example shows how the preceding syntax is used:

SELECT  {Filter( [Customers].[Name].MEMBERS, 
                 [Customers].Properties("Children")="2")} 
   ON ROWS
FROM Sales

In general, property[.VALUE] is a value expression primary and can be used anywhere a value expressioncan be used, including most set value expressions and numeric functions.