Defining Calculated Members

Calculated members are members whose value is dependent on an expression rather than on the value of a cell.

You can define a calculated member using one of the following scopes:

Query scope

The calculated member can be used only within the query in which it is defined. Use the WITH clause in the SELECT statement.

Session scope

The calculated member can be used only within the session in which it is defined, but can be used by multiple queries. Use the CREATE MEMBER statement.

Custom Rollups

In addition to the standard aggregate functions Sum, Min, Max, and Count and Distinct Count, more sophisticated custom rollup functions can be defined for any given member in the CREATE CUBE statement. One common usage scenario is the inventory problem where inventory levels are not summed along the Time dimension, as they would be for every other dimension. That is, if you have one item for seven days of time, you do not have seven items for the week. By using the LastChild function in Multidimensional Expressions (MDX) to define a custom rollup formula, you can automatically roll up closing balances along time.

Examples

A. Creating a Calculated Member

Use the following code to create a calculated member. You must use single quotes to enclose the expression for the calculated member. The OLE DB specification, however, does not require these quotes.

CREATE CUBE MYWAREHOUSE (
DIMENSION . . .
. . . ,
COMMAND (CREATE MEMBER [MYWAREHOUSE].[MEASURES].[WAREHOUSEPROFIT]
AS '[MEASURES].[WAREHOUSE SALES] - [MEASURES].[WAREHOUSE COST]')
)