MDX Create Set / Delete Set

This section shows how to create and delete a named set that persists for the duration of a login session.

A named set is a re-usable member selection that can help streamline the writing and execution of MDX queries.

Syntax

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

 CREATE SET set name AS ' set ' [WHERE [<slicer_specification>]]
|DELETE set_name

Examples

Example 1

The following statement creates a named set called "My Favorite Customers," which is a selection of the top three customers for sales in 2001:

CREATE SET [My Favorite Customers] AS '{ TopCount ([Customer].Individual).Members, 3, ([Measures].[Sales], [Time].[2001])} }'

The following query, issued in the same login session as the CREATE statement, references the stored named set "My Favorite Customers":

SELECT 
{ [Time].[2000], [Time].[2001]} ON COLUMNS
{ [My Favorite Customers] } ON ROWS
FROM Sample.Basic
WHERE ( [Measures].[Profits] )

Example 2

To provide a context, a slicer clause maybe added to the set creation statement, as shown in bold:

CREATE SET [My Favorite Customers] AS
 '{ TopCount ([Customer].Individual).Members, 3, ([Measures].[Sales], [Time].[2001])} } 
   WHERE (East, Toys)'

Notes