Skip to main content

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 metric 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 model cells that belong to any of the hierarchies listed in hierarchySet, else a NULL value is returned.

Examples​

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

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

Returns NULL for sub-model 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-model 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})