Querying on Property Values

OLE DB Programmer's Reference

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 expression can be used, including most set value expressions and numeric functions.

1998-2001 Microsoft Corporation. All rights reserved.