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