Querying Data Warehouses - Data Warehouse Systems: Design and Implementation - page 187

6.1.7 Calculated Members and Named Sets

A powerful concept in MDX is that of calculated members and named sets.
Calculated members are used to define new members in a dimension or new
measures. These are defined using the following clause in front of the SELECT
statement:

WITH MEMBER Parent.MemberName AS expression

where Parent refers to the parent of the new calculated member and
MemberName is its name. Similarly, named sets are used to define new sets
as follows:

WITH SET SetName AS expression

Calculated members and named sets defined using the WITH clause as
above are within the scope of a query. They can be defined instead within the
scope of a session, and thus, they will be visible to all queries in that session
or within the scope of a cube. In these cases, a CREATE statement must be
used. In the sequel, we will only show examples of calculated members and
named sets defined within queries.
Calculated members and named sets are computed at run time, and
therefore, there is no penalty in the processing of the cube or in the number
of aggregations to be stored. The most common use of calculated members is
to define a new measure that relates already defined measures. For example,
a measure that calculates the percentage profit of sales can be defined as
follows:

WITH MEMBER Measures.Profit% AS
(Measures.[Sales Amount] - Measures.[Freight]) /
(Measures.[Sales Amount]), FORMAT STRING = ' #0.00% '

SELECT { [Sales Amount], Freight, Profit% } ON COLUMNS,

Customer.Country ON ROWS
FROM Sales

Here, FORMAT STRING specifies the display format to use for the new
calculated member. In the format expression above, a ' # ' displays a digit
or nothing, while a ' 0 ' displays a digit or a zero. The use of the percent
symbol ' % ' specifies that the calculation returns a percentage and includes a
multiplication by a factor of 100. The result of the query follows:

Sales Amount Freight Profit%

Austria $115,328.31 $6,827.10 94.08%

Belgium $30,505.06 $1,179.53 96.13%

Denmark $32,428.94 $1,377.75 95.75%

Finland $17,530.05 $827.45 95.28%

··· ··· ··· ···