CREATE MEMBER Statement
This statement creates a
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, see Using WITH to Create Calculated Members.
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>]
<cube name> ::= CURRENTCUBE | <Cube Identifier>
<property-definition-list> ::= <property-definition>
| <property-definition>, <property-definition-list>
<property-definition> ::= <property-identifier> = <property-value>
<property-identifier> ::= VISIBLE | SOLVEORDER | FORMAT_STRING| <ole db member properties>
<property-value> ::= <string> | <number>
<optional-scope> ::= <empty> | SESSION
Remarks
The <expression> clause of the calculated member syntax can contain any function that supports Multidimensional Expressions (MDX) syntax. Valid <property-identifier> values are listed later in this topic. Calculated members created with CREATE MEMBER without an <optional-scope> value have session scope. Additionally, strings inside calculated member definitions are delimited with double quotation marks. This is opposite of the method defined by OLE DB, which specifies that strings should be delimited by single quotation marks.
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.
For more information about member properties that are defined by OLE DB, see the OLE DB documentation.
A calculated member can be stored in a
CREATE CUBE MYWAREHOUSE
(
DIMENSION . . .
. . . ,
COMMAND (CREATE MEMBER [MYWAREHOUSE].[MEASURES].[WAREHOUSEPROFIT]
AS '[MEASURES].[WAREHOUSE SALES] - [MEASURES].[WAREHOUSE COST]')
)
Standard Properties
Each calculated member has a set of default properties. When a client application is connected to Microsoft® SQL Server™ 2000 Analysis 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. The following properties represent information relevant to the dimension level in the cube.
| Property identifier | Meaning |
|---|---|
| SolveOrder | The order in which the calculated member will be solved in cases where a calculated member references one more other calculated member (that is, where calculated members intersect each other). |
| Format_String | A Microsoft Office style format string that the client application can use when displaying cell values. |
| Visible | Determines whether the calculated member is visible in a schema rowset. Visible calculated members can be added to a set with the AddCalculatedMembers function. A nonzero value indicates that the calculated member is visible. The default value for this property is Visible.
Calculated members that are not visible (where this value is set to zero) are generally used as intermediate steps in more complex calculated members. These calculated members can also be referred to by other types of members, such as measures. |
Scope
A calculated member can occur within one of the following 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 scope. For more information, see Using WITH to Create Calculated Members.
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.
Examples
Creating Calculated Members
The following code creates two calculated members:
CREATE MEMBER [WAREHOUSE].[MEASURES].[WAREHOUSEPROFIT]
AS '[Measures].[Warehouse Sales] - [Measures].[Warehouse Cost]'
CREATE MEMBER [Warehouse].[Measures].[warehouseprofit]
AS '[Measures].[Warehouse Sales] - [Measures].[Warehouse Cost]',
SOLVEORDER=3