MDX Essentials: Enhancing CROSSJOIN() with Calculated Members - Page 5

April 4, 2005

Add a Calculated Member

In discussions with the information consumers, we have learned that a calculated member, upon which they rely for specific reporting needs, existed in the production cube before its disappearance. The calculated member was called Non-Domestic, and was simply a grouping mechanism to label the non-U.S. Store operations of the FoodMart organization (consisting of activities in Canada and Mexico) for occasional reporting requirements. The effect was thus to allow, side-by-side with the USA, Canada, and Mexico geographical groupings of Stores, the ability to easily see an aggregation of Non-US store values in a simple line item. The consumers use the item for specific and limited purposes only, and realize that it could present a "double count" in scenarios where, for example, a report might be generated that simply listed all groupings, including the "external grouping" provided by Non-Domestic.

Let's create this straightforward calculated member by taking the following steps.

1.  Right-click the new MDX30 OPTIMIZE CROSSJOIN cube.

2.  Select Edit... from the context menu that appears, as depicted in Illustration 7.

Illustration 7: Select Edit... from the Context Menu

The Cube Editor opens.

3.  Right-click the Calculated Members folder in the Tree Pane.

4.  Select New Calculated Member... from the context menu that appears, as shown in Illustration 8.

Illustration 8: Select New Calculated Member... from the Context Menu

The Calculated Member Builder opens.

5.  In the Parent dimension selector, select Store.

6.  Click the Change ... button to the immediate right of the Parent member box.

7.  The Select the parent member dialog opens.

8.  Click All Stores in the dialog to select it, as depicted in Illustration 9.

Illustration 9: Select All Stores as the Parent Member

9.  Click OK to accept the selection and close the dialog.