CREATE MEMBER

The CREATE MEMBER statement allows the creation of calculated members.

You can define a calculated member 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 MEMBER statement. For more information about WITH, see Using WITH to Define Calculated Members, Sets, and Cache.

A newly created calculated member can have several properties such as SolveOrder. The syntax allows setting these properties.

BNF

<create-member-statement> ::= CREATE <optional-scope> <create-member-subset> [<create-member-subset>...]

<create-member-subset> ::= MEMBER <cube-name>.<fully-qualified-member-name> AS '<expression>' [,<property-definition-list>]

<property-definition-list> ::= <property-definition>
  | <property-definition>, <property-definition-list>

<property-definition> ::= <property-identifier> = <property-value>

<property-identifier> ::= <identifier>

<property-value> ::= <string> | <number>

<optional-scope> ::= <empty> | SESSION

Remarks

The <expression> value can contain functions that support multidimensional expressions (MDX) syntax.

The valid <property-identifier> values are listed under “Standard Properties” later in this topic.

Calculated members created with CREATE MEMBER without an <optional-scope> value have session scope.

Example 1

CREATE MEMBER [WAREHOUSE].[MEASURES].[WAREHOUSEPROFIT]

       AS '[MEASURES].[WAREHOUSE SALES] - [MEASURES].[WAREHOUSE COST]'

Example 2

CREATE MEMBER [WAREHOUSE].[MEASURES].[WAREHOUSEPROFIT]

       AS '[MEASURES].[WAREHOUSE SALES] - [MEASURES].[WAREHOUSE COST]',

       SOLVEORDER=3

Scope

A calculated member can occur within one of three scopes:

• Query scope. The visibility and lifetime of the calculated member is limited to the query. The calculated member is defined in an individual query. Query scope overrides session and global scope. For more information, see Using WITH to Define Calculated Members, Sets, and Cache.
• Session scope. The visibility and lifetime of the calculated member is limited to the session in which it is created. (The lifetime is less than the session duration if a DROP MEMBER statement is issued on the calculated member.) The CREATE MEMBER statement is used to create a calculated member with session scope. Session scope overrides global scope.
• Global scope. The visibility and lifetime of the calculated member is not limited to queries or sessions because the calculated member definition is stored (but its data is not). For more information about creating calculated members with global scope, see Creating Calculated Members.

A calculated member can be persisted in a local cube if a CREATE MEMBER statement is specified in the COMMAND clause of the CREATE CUBE statement.

CREATE CUBE MYWAREHOUSE

(

DIMENSION . . .

. . . ,

COMMAND [CREATE MEMBER [MYWAREHOUSE].[MEASURES].[WAREHOUSEPROFIT]

        AS '[MEASURES].[WAREHOUSE SALES] - [MEASURES].[WAREHOUSE COST]']

)

Quoted Strings in Calculated Member Definitions

Strings inside calculated member definitions are delimited with double quotation marks.

Standard Properties

Each calculated member has a set of default properties. When a client is connected to Microsoft® SQL Server™ OLAP Services, the default properties are either supported or available to be supported, as the administrator chooses.

Additional member properties may be available, depending upon the cube definition. These properties represent information relevant to the dimension level in the cube.