Cube-Based MDX Calculations - Microsoft SQL Server 2008 Analysis Services - page 189

calculated member, memberC = 5 , as a child of the member ALL , the value of the cell corre-
sponding to the member ALL doesn't change. It remains the sum of the real members
memberA and memberB (that is, 3 ).

Defining Calculated Members

There are three ways to create calculated members, and they differ only in the lifetime of
the member:

. A calculated member created using the WITH clause of a SELECT statement is available

in the scope of the current query execution.

. A calculated member created using the CREATE MEMBER statement is available in the

scope of a session. It is generally available to subsequent queries from the same user.
For more information about sessions, see Chapter 32, “XML for Analysis.”

. A calculated member defined in an MDX script is available to all users of the cube.

Let's look at a simple example: You want to write an expression that returns not only
values for Store Sales and Store Cost , but also the profit from those sales. To that end,
you define a calculated measure (a calculated member on a measure dimension) [Profit] =
[Store Cost] - [Store Sales] .

To create the calculated member, follow the steps listed here in BI Dev Studio. By doing
so, the calculated member will be available for use by all the users of the cube.

1. In BI Dev Studio, open the FoodMart 2008 project.
2. In the Solution Explorer, double-click the Warehouse and Sales cube to open the
cube editor.
3. On the Calculations tab, right-click anywhere in the Script Organizer, and choose
New Calculated Member from the contextual menu.
4. Type the name of your calculated member— Profit —in the Name text box .

NOTE
Because a calculated member is a new dimension member, it's created as member
one of the dimension hierarchies. When you create the calculated member, you need to
specify a hierarchy to which calculated member belongs. You can also specify the mem-
ber that should be its parent or you can omit the parent. If you omit the parent, the
system will create the calculated member on the top level of the hierarchy.

5. Specify Measures as the parent hierarchy.