Calculated Members

Analysis Services Programming

Analysis Services Programming

Calculated Members

Calculated members are members whose values depend on an expression rather than 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.

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

Use the following code to create a calculated member:

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

Note  You must use single quotes (') to enclose the expression for the calculated member even though the OLE DB specification does not require these quotes.

For more information, see CREATE CUBE Statement.

Custom Rollups

In addition to the standard rollup (that is, aggregate) functions Sum, Min, Max, and 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 might be for other dimensions. For example, if you have one item in inventory on seven consecutive days, you do not have a total of 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.

See Also

Calculated Members