Except

The MDX Except function for Essbase returns a subset containing the differences between two sets, optionally retaining duplicates. The two input sets must have identical dimensionality.

Syntax

Except ( set1, set2 [,ALL] )

Parameters

set1

A set to compare with set2.

set2

A set to comparet with set1.

ALL

The optional ALL flag retains duplicates. Matching duplicates in set1 and set2 are eliminated.

Example

Except( {[New York], [California], [Florida], [California]},
        {[Oregon], [Washington], [California], [Florida]})

returns {[New York]}.

Except( {[New York], [California], [Florida], [California]},
        {[Oregon], [Washington], [California], [Florida]}, ALL)

returns {[New York], [California]}.

The following query returns Actual Sales and Profit numbers for the level-0 markets that are not defined as "Major Market."

SELECT
 {[Measures].[Sales], [Measures].[Profit]} 
ON COLUMNS,
 Except(
   [Market].Levels(0).Members, 
   UDA (Market, "Major Market")
 ) ON ROWS
FROM Sample.Basic
WHERE {([Year].[Qtr1], [Scenario].[Actual])}

This query returns the grid:

Table 4-61 Output Grid from MDX Example

(axis)	Sales	Profit
Connecticut	3472	920
New Hampshire	1652	202
Oregon	5058	1277
Washington	4835	1212
Utah	4209	744
Nevada	6516	775
Oklahoma	2961	718
Louisiana	2906	773
New Mexico	1741	4
Wisconsin	4073	913
Missouri	3062	399
Iowa	6175	2036