Skip to main content
Version: I2025.5.x

NULLEXCEPT

Evaluates the given tuple expression when the query includes any of the hierarchies listed in the hierarchySet argument, else returns NULL. Totals that hierarchySet contributes to are displayed, else a NULL value is displayed for the total (this applies to both subtotals and grand totals).

Syntax​

NULLEXCEPT(tupleExpression, hierarchySet[, followQueryLevelAliases])

Input Parameters​

tupleExpression

Required. The tuple expression to evaluate. This can be in any supported tuple syntax, including:

  • Canonical static tuple syntax, including key and name member references.
  • Canonical dynamic tuple syntax that uses currentMember.
  • Dimensionally-modified tuple syntax, such as currentMember.lag(), currentMember.parent().
  • A measure reference. In this case, all other dimension hierarchies are implicitly set to the ALL member.

hierarchySet

Required. A set of one or more hierarchies. AtScale returns the tuple's value only for cube cells that belong to any of the hierarchies listed in hierarchySet, else a NULL value is returned.

followQueryLevelAliases

Optional. Determines whether NULLEXCEPT searches the query for dereferenced level alias hierarchies, as well as the explicit list of attributes in the hierarchySet.

Supported values:

  • True: The tupleExpression is emitted when the query selects a hierarchy or secondary attribute from the hierarchySet list. Or, if the query includes level aliases, they are converted to their referenced hierarchies before matching. This enables level aliases in the query to also match against the hierarchySet contents.
  • False: Default. The tupleExpression is emitted only when the query selects a hierarchy or secondary attribute from the hierarchySet list.

If you do not specify followQueryLevelAliases, it defaults to False.

Examples​

Returns NULL for sub-cube cells that don't include Mydim.MyHierarchy, else returns Measures.[salesAmount]:

NULLEXCEPT(Measures.[salesAmount], {Mydim.MyHierarchy})

Returns NULL for sub-cube cells that don't include either Mydim.MyHierarchy, or Mydim2.MyHierarchy2, else returns Measures.[salesAmount]:

NULLEXCEPT(Measures.[salesAmount], {Mydim.MyHierarchy, Mydim2.MyHierarchy2})

Returns NULL for sub-cube cells that don't include either Mydim.MyHierarchy or Mydim2.MyHierarchy2, else returns (Mydim.MyHierarchy.currentMember.lag(1), Measures.[salesAmount]).

NULLEXCEPT((Mydim.MyHierarchy.currentMember.lag(1), Measures.[salesAmount]), {Mydim.MyHierarchy, Mydim2.MyHierarchy2})

Returns 1 for queries that contain level aliases for the [DateCustom].[StandardMonth] hierarchy, else returns NULL:

NULLEXCEPT(1, {[DateCustom].[StandardMonth]}, True)

Returns NULL for for queries that contain level aliases for the [DateCustom].[StandardMonth] hierarchy:

NULLEXCEPT(1, {[DateCustom].[StandardMonth]}, False)