MDAC 2.5 SDK - OLE DB Programmer's Reference
Chapter 24: Multidimensional Expressions

Defining a New Member Example

On the ROWS axis, show the Actual Sales and percent change from last year's sales for the product lines for which (Sales,Actual) is less than (Sales,Budget). This should be sorted by Sales Last Year. On the COLUMNS axis, show the top two cities in the top two countries in the regions Europe and America.

The dataset defined above is retrieved by the following MDX statement:

WITH MEMBER
   Measures.[% Change from Last Year Sales] AS Sales / 
      (Sales, Year.PREVMEMBER)
SELECT GENERATE(
   GENERATE({America, Europe},
            TOPCOUNT(Geography.CURRENTMEMBER.CHILDREN, 2, Sales)),
   TOPCOUNT(Geography.CURRENTMEMBER.CHILDREN, 2, Sales)) ON COLUMNS, 
   CROSSJOIN({Sales, [% Change from Last Year Sales]},
             ORDER(FILTER([Product Lines].MEMBERS, 
             (Sales,Actual)<(Sales,Budget)),
             (Sales, Year.PREVMEMBER))) ON ROWS
FROM SalesCube
WHERE ([1997], Actual)

The WITH MEMBER clause defines a new member named "% Change from Last Year Sales." This appears on the Measures dimension (as indicated by the qualifier [Measures]). This member is specified by the formula Sales/(Sales, Year.PREVMEMBER). To yield the required result, this new member is evaluated, within the context of the rest of the expression, for each value in the n-dimensional manifold selected by the dataset.