Jaa


Except (MDX)

Evaluates two sets and removes those tuples in the first set that also exist in the second set, optionally retaining duplicates.

Syntax

Except(Set_Expression1, Set_Expression2 [, ALL ] )

Arguments

  • Set_Expression1
    A valid Multidimensional Expressions (MDX) expression that returns a set.
  • Set_Expression2
    A valid Multidimensional Expressions (MDX) expression that returns a set.

Remarks

If ALL is specified, the function retains duplicates found in the first set; duplicates found in the second set will still be removed. The members are returned in the order they appear in the first set.

Examples

The following example demonstrates the use of this function.

   This query shows the quantity of orders for all products,
   with the exception of Components, which are not
   sold.
SELECT 
   [Date].[Month of Year].Children  ON COLUMNS,
   Except
      ([Product].[Product Categories].[All].Children ,
         {[Product].[Product Categories].[Components]}
      ) ON ROWS
FROM
   [Adventure Works]
WHERE
   ([Measures].[Order Quantity])

See Also

Reference

- (Except) (MDX)
MDX Function Reference (MDX)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

17 July 2006

Changed content:
  • Updated syntax and arguments to improve clarity.
  • Added updated examples.