Introduction to MDX - Professional SQL Server Analysis Services 2005 with MDX - page 109

[ , SOVLE_ORDER = <integer>]
[ , <CellProperty> =
<PropertyExpression>]
MDX uses the keywords MEMBER and AS in the WITH clause for creating
calculated members. The MemberName should be a fully qualified member
name that includes the dimension, hierarchy, and level under which the spe-
cific calculated member needs to be created. The MDX_Expression should
return a value that corresponds to the member. The SOLVE_ORDER, which
is an optional parameter, should be a positive integer value if specified. The
SOLVE_ORDER determines the order in which the members need to be eval-
uated when multiple calculated members are defined. The CellProperty is
also an optional parameter and it is used to specify cell properties for the cal-
culated member such as text formatting of the cell contents including back-
ground color.
All the measures in a cube are stored in a special dimension called Meas-
ures. Calculated members can also be created on the measures dimension.
In fact, most of the calculated members that are used for business are usually
on the measures dimension. Calculated members on the measures dimen-
sion are referred to as calculated measures. Following are examples of calcu-
lated member statements.
Example 1
WITH MEMBER [MEASURES].[Profit] AS
'([Measures].[Internet Sales Amount] -
[Measures].[Total Product Cost])'
SELECT [MEASURES].[Profit] ON COLUMNS,
[Customer].[Country].MEMBERS ON ROWS
FROM [Adventure Works]
In example 1 a calculated member Profit has been defined as the difference
of the measures [Internet Sales Amount] and [Total Product Cost]. When the
query is executed for every country, this calculated member will be evaluated
based on the MDX expression.