MDX NULL Property Values

When you query Essbase properties using MDX, note that not all members may have valid values for a requested property name. A NULL value would be returned in such cases.

For example, the MEMBER_ALIAS property returns an alternate name for a member; however, not all members may have aliases defined.

In the following query, assume none of the members in the Year dimension have aliases defined for them. Therefore, the query returns NULL values for the MEMBER_ALIAS property for members in the Year dimension:

 SELECT
  [Year].Members
     DIMENSION PROPERTIES MEMBER_ALIAS on columns

The attribute properties are defined for members of a specific dimension and a specific level in that dimension. In the Sample Basic database, the [Ounces] property is defined only for level-0 members of the Product dimension.

Therefore, if you query for the [Ounces] property of a member from the Market dimension, as shown in the following query, you will get a syntax error:

      
 SELECT
    Filter([Market].members,
           [Market].CurrentMember.[Ounces] = 32) on columns

Additionally, if you query for the [Ounces] property of a non level-0 member of the dimension, you will get a NULL value.

When using property values in value expressions, you can use the function IsValid() to check for NULL values. The following query returns all Product dimension members with [Ounces] property value of 12, after eliminating members with NULL values.

           
 Select
    Filter([Product].Members,
           IsValid([Product].CurrentMember.[Ounces]) and
           [Product].CurrentMember.[Ounces] = 12) on columns