Returns a set of all elements from the first set that do not belong to the second set.

Except( {A,B,C}, {A,F,G} ) -> {B,C}

Equivalent syntax : {A,B,C} - {A,F,G}

The ALL flag parameter specifies that the function should retain duplicates; if the ALL flag is not set then \
all the duplicates are removed from the result set.

Examples

Except(member-set,member-set)

Returns all members from the first set that are not present in the second. Duplicates are removed.

In our example, a single instance of [America] is returned.

Query

Except({[Geography].[Geo].[Europe],[Geography].[Geo].[America],[Geography].[Geo].[America]},[Geography].[Geo].[Europe])

Result

{ 
  [Geography].[Geo].[Continent].[America]
}

Assertion : MDX Equals

{ [Geography].[Geo].[America] }

Assertion : MDX Equals

// Equivalent to the operator -
{[Geography].[Geo].[Europe],[Geography].[Geo].[America],[Geography].[Geo].[America]} - {[Geography].[Geo].[Europe]}

Assertion : MDX Equals

// Duplicates are removed
Except({[Geography].[Geo].[Europe],[Geography].[Geo].[America],[Geography].[Geo].[America]},[Geography].[Geo].[Europe])

Except(tuple-set,tuple-set)

Returns all tuples from the first set that are not present in the second.

In our example, ([Geography].[Geo].[Europe],[Product].[Prod].[Corporate]) is returned.

Query

Except({[Geography].[Geo].[Europe],[Geography].[Geo].[America]} * [Product].[Prod].[Corporate],{([Geography].[Geo].[America],[Product].[Prod].[Corporate])} )

Result

{
  ( [Geography].[Geo].[Continent].[Europe], [Product].[Licence].[Corporate] )
}

Assertion : MDX Equals

{([Geography].[Geo].[Europe],[Product].[Prod].[Corporate])}

Except(member-set,member-set,ALL)

Returns all elements from the first set that are not present in the second. Duplicated elements are kept.

In our example, two instances of {[America], [America]} are returned.

Query

Except({[Geography].[Geo].[Europe],[Geography].[Geo].[America],[Geography].[Geo].[America]},{[Geography].[Geo].[Europe]},ALL)

Result

{ 
  [Geography].[Geo].[Continent].[America], 
  [Geography].[Geo].[Continent].[America]
}

Assertion : MDX Equals

{[Geography].[Geo].[America],[Geography].[Geo].[America]}

Assertion : MDX Equals

Except({[Geography].[Geo].[Europe],[Geography].[Geo].[America],[Geography].[Geo].[America]},{[Geography].[Geo].[Europe]},all/*lower case*/)

Special Examples (errors, null, ...)

Except(member-set)

Applying except to a single set returns an error.

Query

Except( [Geography].[Geo].members )

Result


Error Code    : OLAP_FUNCTION_UNEXPECTED_ARGUMENT_COUNT
Error Message : Except() : unexpected number of parameters '1' (expected:'2:3')

Except({})

Applying except to empty sets results in an empty set. Remember null is implicitly converted to an empty set.

Query

{}

Result

{}

Assertion : MDX Equals

Except({},{})

Assertion : MDX Equals

Except(null,null)

Except(member-set,member-set) - members with different dimensionality

Except can only be applied to sets with the same dimensionality. Therefore all members/tuples should contain the same hierarchies.

Query

Except( [Geography].[Geo].members, {[Geography].[Economy].DefaultMember} )

Result


Error Code    : OLAP_INVALID_TUPLE_DIMENSIONALITY
Error Message : invalid tuple dimensionality '[Geography].[Geo]' - '[Geography].[Economy]'

Except(tuple-set,tuple-set) - tuples with different dimensionality

Except can only be applied to sets with the same dimensionality. This will also happen if tuples have the same hierarchies but in a different order

Query

Except(
{ ( [Geography].[Geo].[Spain], [Time].[Calendar].[2010] ) },
{ ( [Time].[Calendar].[2010] , [Geography].[Geo].[Spain] ) }
)

Result


Error Code    : OLAP_INVALID_TUPLE_DIMENSIONALITY
Error Message : invalid tuple dimensionality '[Geography].[Geo],[Time].[Calendar]' - '[Time].[Calendar],[Geography].[Geo]'

Except(member-set,member-set,INVALID_FLAG)

An invalid flag generates an error.

Query

Except( {}, {}, INVALID_FLAG)

Result


Error Code    : OLAP_FUNCTION_ARGUMENT_TYPE_MISMATCH
Error Message : Except() : argument (2) type mismatch: expected:'[ALL]', got:'function option(INVALID_FLAG)'

XMLA Properties

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

icCube OLAP version: 2.5.1 13-May-2012 02:21:10 GMT

Parameters

Return

Description

See Also

Examples

Special Examples (errors, null, ...)

XMLA Properties