Skip to main content
Version: I2024.2.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)

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.

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})