Except

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

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
WHERE {([Year].[Qtr1], [Scenario].[Actual])}

This query returns the grid:

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

©2004 Hyperion Solutions Corporation. All Rights Reserved.
http://www.hyperion.com