MDX Create Set / Delete Set

In MDX, you can create and delete a named set that persists for the duration of an Essbase login session. A named set is a re-usable member selection that can help you streamline the writing and execution of MDX queries.

Syntax

The syntax to create or delete session-persistent named sets is shown below:

 CREATE SET <set_name> AS '<set>' [FROM <cube_specification>] [WHERE [<slicer_specification>]]
|DROP SET <set_name> [FROM <cube_specification>]

Example 1

The following statement creates a named set called "Most Selling Products," which is a selection of the top selling products for Qtr1:

CREATE SET [Most Selling Products] AS 
 '
  {TopCount
   (
     Descendants
     (
     [Product], [Product].level, AFTER
     ), 3, 
     ([Measures].[Sales], [Year].[Qtr1])
   )
  }
 '

The following query, issued in the same login session as the CREATE statement, references the stored named set "Most Selling Products":

SELECT {[Measures].[Sales]} 
 ON COLUMNS, 
{[Most Selling Products]} 
ON ROWS 
FROM [Sample.Basic]

Example 2

To provide a context, a slicer clause may be added to the set creation statement. The slicer clause begins with WHERE:

CREATE SET [Most Selling Products] AS 
 '
  {TopCount
   (
     Descendants
     (
     [Product], [Product].level, AFTER
     ), 3, 
     ([Measures].[Sales], [Year].[Qtr1])
   )
  }
 '  WHERE ([Market].[East], [Scenario].[Actual])

Notes

Only 16 session-based named sets maybe stored simultaneously.
Named set definitions may not contain references to other named sets.