Querying for Member Properties in MDX

Learn how to query for Essbase member properties using MDX.

Properties can be used inside an MDX query in two ways. In the first approach, you can list the dimension and property combinations for each axis set. When a query is executed, the specified property is evaluated for all members from the specified dimension and included in the result set.

For example, on the column axis, the following query will return the GEN_NUMBER information for every Market dimension member. On the row axis, the query returns MEMBER_ALIAS information for every Product dimension member.

SELECT
 [Market].Members
    DIMENSION PROPERTIES [Market].[GEN_NUMBER] on columns,
 Filter ([Product].Members, Sales > 5000)
    DIMENSION PROPERTIES [Product].[MEMBER_ALIAS] on rows
from Sample.Basic

When querying for member properties using the DIMENSION PROPERTIES section of an axis, a property can be identified by the dimension name and the name of the property, or just by using the property name itself. When a property name is used by itself, that property information is returned for all members from all dimensions on that axis, for which that property applies.

Note:

When a property name is used by itself within the DIMENSION PROPERTIES section, do not use brackets [] around the property name.

In the following query. the MEMBER_ALIAS property is evaluated on the row axis for both Year and Product dimensions.

SELECT
 [Market].Members
    DIMENSION PROPERTIES [Market].[GEN_NUMBER] on columns,
 CrossJoin([Product].Children, Year.Children)
    DIMENSION PROPERTIES MEMBER_ALIAS on rows
from Sample.Basic

In a second approach, properties can be used inside value expressions in an MDX query. For example you can filter a set based on a value expression that uses properties of members in input set.

The following query returns all caffeinated products that are packaged in cans.

         
Select
Filter([Product].levels(0).members,
         [Product].CurrentMember.Caffeinated and
         [Product].CurrentMember.[Pkg Type] = "Can")
         Dimension Properties
           [Caffeinated], [Pkg Type] on columns

The following query uses the UDA [Major Market] to calculate the value [BudgetedExpenses] based on whether the current member of the Market dimension is a major market or not.

With 
   MEMBER [Measures].[BudgetedExpenses] AS
      'IIF([Market].CurrentMember.[Major Market],
       [Marketing] * 1.2, [Marketing])'
Select
   {[Measures].[BudgetedExpenses]} on columns,
   Market.Members on rows
Where
  ([Budget])

The following queries use alias table names.

 SELECT
 [Product].Members
  DIMENSION PROPERTIES [Default] on columns
from Sample.Basic;

SELECT
 [Product].Members
  DIMENSION PROPERTIES [Long Names] on columns
from Sample.Basic;