MDX NULL Property Values

Learn about how Essbase handles null property values when you query using MDX.

Not all members may have valid values for a given property name. For example, the MEMBER_ALIAS property returns an alternate name for a given member as defined in the outline; however, not all members may have aliases defined. In these cases A NULL value would be returned for those members that do not have aliases.

In the following query:

 SELECT
  [Year].Members
     DIMENSION PROPERTIES MEMBER_ALIAS on columns

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.

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