Neutral Element (MDX+)

A neutral element defined as ∅ (U+2205)

Description

The neutral element - ∅, neutralElement() - has no effect on all operations. For example:


∅ * {A} = {A}
∅ + {A} = {A}
(∅,[Switzerland]) = ([Switzerland])

FILTER BY ∅ - does not filter anything
WHERE (∅) - is like no where clause

It might be created using the neutralElement() function.

This is mainly used in the reporting to define filters that have no effect.

It is defined as the Unicode character ∅ (U+2205).

On Windows you might try to input with the ALT key pressed 2205 on your keypad (it might not work)
On Linux you can input that character using : CTRL + SHIFT + U followed by 2205 and ENTER.

Examples

Sum - ∅ + {A}

Switzerland
248

Product - ∅ * {A}

Evaluates the product of the neutral element with a set

Query

SELECT
    ∅ * {[Geography].[Economy].[Switzerland]} ON 0
FROM
    [Sales]

Result

Switzerland
248

Switzerland

248

Assertion : MDX Equals

SELECT
     crossjoin( ∅, {[Geography].[Economy].[Switzerland]} ) ON 0
FROM
    [Sales]

Assertion : MDX Equals

SELECT
    {[Geography].[Economy].[Switzerland]} ON 0
FROM
    [Sales]

Tuple - (∅,A)

Switzerland
248

Where clause

Switzerland
248

Subselect

Using the Neutral element in a subselect

Query

SELECT
    {[Geography].[Economy].[Switzerland]} ON 0
FROM
  ( select ∅ on 0 from [Sales] )

Result

Switzerland
248

Switzerland

248

Assertion : MDX Equals

SELECT
    {[Geography].[Economy].[Switzerland]} ON 0
FROM
    [Sales]

Assertion : Cell Equals

SELECT
    [Geography].[Economy].defaultMember ON 0
FROM
( select ∅ on 0, {[Geography].[Economy].[Switzerland]} ON 1 from [Sales] )

FilterBy

Using the Neutral element in a filterBy

Query

SELECT
    {[Geography].[Economy].[Switzerland]} ON 0
FROM
  [Sales]
FILTERBY ∅

Result

Switzerland
248

Switzerland

248

Assertion : MDX Equals

SELECT
    {[Geography].[Economy].[Switzerland]} ON 0
FROM
    [Sales]

Assertion : MDX Equals

SELECT
    ∅ * {∅ + {(∅,[Geography].[Economy].[Switzerland],∅,∅)}} ON 0
FROM
    (SELECT ∅ ON 0 FROM [Sales])
WHERE ∅
FILTERBY ∅

Union, Intersection, Except and Distinct

Using the Neutral element with Union, Intersection, Except and Distinct

Query

WITH
  MEMBER Union1 as Union( ∅, {[Geography].[Economy].[Switzerland]} ) is {[Geography].[Economy].[Switzerland]}
  MEMBER Union2 as Union( {[Geography].[Economy].[Switzerland]}, ∅ ) is {[Geography].[Economy].[Switzerland]}
  MEMBER Union3 as Union( ∅, ∅ ) is ∅
  MEMBER Intersect1 as Intersect( ∅, {[Geography].[Economy].[Switzerland]} ) is {[Geography].[Economy].[Switzerland]}
  MEMBER Intersect2 as Intersect( {[Geography].[Economy].[Switzerland]}, ∅ ) is {[Geography].[Economy].[Switzerland]}
  MEMBER Intersect3 as Intersect( ∅, ∅ ) is ∅
  MEMBER Except1 as Except( {[Geography].[Economy].[Switzerland]}, ∅ ) is {[Geography].[Economy].[Switzerland]}
  MEMBER Except2 as Except( ∅,{[Geography].[Economy].[Switzerland]} ) is ∅
  MEMBER Except3 as Except( ∅, ∅ ) is ∅
  MEMBER Distinct1 as Distinct( ∅ ) is ∅
SELECT
    {Union1,Union2,Union3,Intersect1,Intersect2,Intersect3,Except1,Except2,Except3,Distinct1} ON 0
FROM
  [Sales]

Result

Union1	Union2	Union3	Intersect1	Intersect2	Intersect3	Except1	Except2	Except3	Distinct1
true	true	true	true	true	true	true	true	true	true

Assertion : MDX Equals

WITH
  MEMBER Union1 as true
  MEMBER Union2 as true
  MEMBER Union3 as true
  MEMBER Intersect1 as true
  MEMBER Intersect2 as true
  MEMBER Intersect3 as true
  MEMBER Except1 as true
  MEMBER Except2 as true
  MEMBER Except3 as true
  MEMBER Distinct1 as true
SELECT
    {Union1,Union2,Union3,Intersect1,Intersect2,Intersect3,Except1,Except2,Except3,Distinct1} ON 0
FROM
  [Sales]

isNeutral()

You can test if an MDX entity is the neutral element with isNeutral function or is.

Query

WITH
  MEMBER test as isNeutral(∅)
SELECT
    test ON 0
FROM
  [Sales]

Result

test
true

test

true

Assertion : MDX Equals

WITH
  MEMBER test as true
SELECT
    test ON 0
FROM
  [Sales]

Assertion : MDX Equals

WITH
  MEMBER test as ∅ is ∅
SELECT
    test ON 0
FROM
  [Sales]

NeutralElement()

You can use NeutralElement function to return the neutral element

Query

WITH
  MEMBER test as ∅ is NeutralElement()
SELECT
    test ON 0
FROM
  [Sales]

Result

test
true

test

true

Assertion : MDX Equals

WITH
  MEMBER test as true
SELECT
    test ON 0
FROM
  [Sales]

Assertion : MDX Equals

WITH
  MEMBER test as ∅ is ∅
SELECT
    test ON 0
FROM
  [Sales]

Special Examples (errors, null, ...)

Axis

Lambda Function

Lambda Function (2)

XX
#ERROR : (OLAP_NEUTRAL_ELEMENT_MISMATCH)

Eval Function

XX
#ERROR : (OLAP_NEUTRAL_ELEMENT_MISMATCH)

Since Version

6.8

XMLA Properties

Find here the value of the XMLA properties when not specified otherwise.