Introduction to MSSQL Server Analysis Services: Presentation Nuances: CrossTab View - Same Dimension - Page 5

April 11, 2005

Create a Member Property to Support a Virtual Dimension

The key to accommodating the need to present a dimension in two axes will be the creation of a virtual dimension, which will itself be based upon a member property that we establish. Because the member property we need will belong to the Store dimension, we will begin our alternations within the Shared Dimensions folder, where we will take the following actions to accomplish our objectives:

1.  Expand the Shared Dimensions folder within the FoodMart 2000 Analysis Services database.

2.  Right-click the Store dimension within the Shared Dimensions folder.

3.  Select Edit from the context menu that appears, as depicted in Illustration 9.

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

The Dimension Editor opens.

4.  Expand the lowest level in the hierarchy, Store Name.

5.  Expand the Member Properties folder, as shown in Illustration 10.

Illustration 10: Expanded Member Properties Folder ...

We will be adding a new member property to those that already exist for the Store dimension.

6.  Right-click the expanded Member Properties folder.

7.  Select New Member Property from the context menu that appears, as depicted in Illustration 11.

Illustration 11: Select New Member Property ...

The Insert Member Property dialog appears.

8.  Select store_city as the source column for the new member property, as shown in Illustration 12.

Illustration 12: Select Store_City as the Source Column

We will adjust the appearance of the Store City information next, to meet the business requirement for a "City, State" presentation.

9.  Click OK to add the new member property.

The dialog closes, and the Store City member property appears in the Member Properties folder, underneath the existing member properties.

10.  With the new Store City member property selected, replace the following entry in the Source Column box of the Properties pane - Basic tab:


with the following entry:

"store"."store_city"+', '+"store"."store_state"

The Source Column box of the Properties pane - Basic tab appears as depicted in Illustration 13.

Illustration 13: The Edited Source Column Entry ...

11.  Select File -> Exit from the Dimension Editor main menu.

A Dimension Editor message box appears, asking if we want to save changes, as shown in Illustration 14.

Illustration 14: Click Yes to Save Modifications to the Store Dimension

12.  Click Yes to save changes to the Store dimension and to close the Dimension Editor.

We return to Analysis Manager, where we are now ready to create a Virtual Dimension to support dual axis reporting with the same (Store) dimension.