CREATE MEMBER, DROP MEMBER, ALTER MEMBER Statements

The CREATE MEMBER, DROP MEMBER, and ALTER MEMBER statements allow the creation, deletion, and modification of calculated members. Calculated members can be defined in the scope of a single query (as part of the DEFINE clause in the SELECT statement, or on the session level (by using the CREATE MEMBER statement). A newly created member may have several properties such as Name, Expression, Format, Fore_Color and Back_Color. The syntax allows setting these properties.

BNF

Member functions serve to extend an existing Dimension and Level with an additional member.

<MDX statement> ::= <select-clause-list> <from-clause> <optional-where-clause>
<optional-define-formula-clause-list> <optional-define-set-clause-list>
| <create-formula-clause>
| <alter-formula-clause>
| <drop-formula-clause>
| <create-set-clause>
| <alter-set-clause>
| <drop-set-clause>

<optional-define-formula-clause-list> ::= <empty>
| <define-formula-clause-list>

<define-formula-clause-list> ::= <define-formula-clause>
| <define-formula-clause>, <define-formula-clause-list>

<define-formula-clause> ::= DEFINE <define-formula-subset>

<create-formula-clause> ::= CREATE <optional-scope> <define-formula-subset>

<alter-formula-clause> ::= ALTER <define-formula-subset>

<drop-formula-clause> ::= DROP MEMBER <fully-qualified-member-name>

<define-formula-subset> ::= MEMBER <fully-qualified-member-name> AS
<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-define-set-clause-list> ::= <empty>
| <define-set-clause-list>

<define-set-clause-list> ::= <define-set-clause>
| <define-set-clause>, <define-set-clause-list>

<define-set-clause> ::= DEFINE <define-set-subset>

<create-set-clause> ::= CREATE <optional-scope> <define-set-subset>

<alter-set-clause> ::= ALTER <define-set-subset>

<drop-set-clause> ::= DROP SET <set-name>

<define-set-subset> ::= SET <set-expression> AS <set-name>

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

Example

select … define member Measures.Measure.Margin AS
   expression = "sales - cost",
   forecolor = "if( margin < 0, red, green )"

select topnpro on rows
   { cxt, scale, margin } on columns
   from sales
   define member sales.measures.margin as
      expression = "sales - cost"
   define set topcount(products,10,5.98) AS topnpro

create member Products.Product.[Office] AS
expression = "[Word] + [Excel] + [Access]"

Scope

There are several scopes an object (formula or set) may occur within. A separate syntax is used for each scope.

Query scope. The visibility and lifetime of the object is limited to the query. The formula is defined in an individual query. Query scope overloads session and global scope.
Session scope. The visibility and lifetime of the object is limited to the session that created it. Session scope overloads global scope.
Global scope. The visibility extends to other users, and its lifetime extends beyond the session from which it was created.

Notes

Objects specified within an individual query have query scope.
Objects not specifying a scope have session scope.
ALTER and DROP always affect the most local scope (i.e. session before global).

Quoted Strings in Member Definitions

Strings inside member definitions are delimited with either single or double quotes. These can be nested arbitrarily by alternating single and double quotes.

Standard Properties

Some standard property names exist. When connected to a Microsoft^® OLAP Server, the list of standard properties is either supported or available to be supported, as the Administrator chooses.

Additional properties may be available. The properties depend upon the cube definition, and represent information relevant to the Dimension / Level in the cube.

Property	Meaning
Expression	Value, an additive numeric quantity. It need not be additive, since it might be used only as a displayed member. For instance, percentages might be returned as the result of a calculation.
SolveOrder	The order in which the formula will be solved in case of intersection with other formulas.
Format_String	MS Office format string - for the cell values
Alignment	0-Left, 1-right, 2-center - for the cell values
Fore_Color	RGB value – for the cell values
Back_Color	RGB value – for the cell values
Font_Name	The font name - for the cell values
Font_Size	The font size - for the cell values
…

Ambiguities and Name Conflicts

Ambiguities (name conflicts) can arise. It is the responsibility of the client application to provide unambiguous (qualified) names. It is the responsibility of the provider code to warn of potential ambiguities, or to provide notification (alerts?) if ambiguities exist. To prevent name conflicts, the following rules apply:

In the INSERT INTO statement, level name can be qualified with dimension name. Measure name can be qualified with the keyword MEASURES. For example:

Create Cube myCube (
   Dimension Products,
      Level ID,
   Dimension Customer,
      Level ID,
   Measure ID);
Insert Into myCube (Products.ID, Customer.ID, Measures.ID) Select Dim_Products.ID, Dim_Customers.ID, Sales_ID From Dim_Products, Dim_Customers, Sales Where Sales.ID=Products.ID AND Sales.ID = Customers.ID;

Existing members may not be overridden. For instance, one cannot create a new member function "WA" if "WA" already exists.

SELECT

The SELECT statement is described in the OLE DB for OLAP specification.