CREATE SET Statement
This statement creates user-defined sets.
You can define a set for use by a single query with the WITH clause in the SELECT statement or for use in multiple queries in a session with the CREATE SET statement. For more information about WITH, see Using WITH to Create Named Sets.
BNF
<create-set-statement> ::= CREATE <optional-scope> <create-set-subset> [<create-set-subset>...]
<create-set-subset> ::= SET <cube-name>.<set-name> AS '<set-expression>'
<cube name> ::= CURRENTCUBE | <Cube Identifier>
<optional-scope> ::= <empty> | SESSION
Remarks
A named set is a set of dimension members (or an expression that defines a set) that is created to be used again. For example, by using a named set it is possible to define a set of dimension members that consists of the set of top 10 stores by sales. This set can be defined statically, or by means of a function like TOPCOUNT. This named set can then be used wherever the set of top 10 stores is needed.
The <expression> clause of the calculated member syntax can contain any function that supports Multidimensional Expressions (MDX) syntax. Sets created with the CREATE SET statement that do not specify an <optional-scope> clause have session scope.
It is an error to specify a cube other than that to which it is currently connected. Therefore, you should use CURRENTCUBE in place of a cube name to denote the current cube.
Scope
A user-defined set can occur within one of the following scopes:
Query scope
The visibility and lifetime of the set is limited to the query. The set is defined in an individual query. Query scope overrides session scope. For more information, see Using WITH to Create Named Sets.
Session scope
The visibility and lifetime of the set is limited to the session in which it is created. (The lifetime is less than the session duration if a DROP SET statement is issued on the set.) The CREATE SET statement is used to create a set with session scope.
Examples
A. Creating a Named Set Using a Function Expression
The following example creates a named set consisting of the top ten stores, as ranked by their sales, in the Sales cube:
CREATE SET [Sales].[TopStores] as
'TopCount([Store].Members,10,[Measures].[Store Sales])'B. Creating a Named Set Using a Set Expression
In this example, a named set is statically defined to consist of states in the Northwest region of the United States:
CREATE SET [Sales].[NorthwesternStores] as
'{ [Store].[All Stores].[USA].[WA],
[Store].[All Stores].[USA].[OR],
[Store].[All Stores].[USA].[ID] }'