MDX in Analysis Services: Calculated Members: Leveraging Member Properties - Page 3

November 24, 2003

The purpose of the first-step core query is simply to set up the axes for the enhancements we will make to allow the retrieval and display of member properties. As a useful way to contrast the behavior of the text-string property that we will be attempting to present as a number, we have included a numerical value from the cube, Store Sales, simply to compare to the Store SqFt member property that we will be retrieving.

7.  Save the query with changes as MXAS09-1.

We will now add the syntax required to create a calculated member based upon the Store SqFt member property.

8.  Modify the comment line in the query to read as follows:

-- MXAS09 - 2: Tutorial Query - Step 2

9.  Add the following before the SELECT statement:

 MEMBER [Measures].[Store Size] AS 
 'Val(Store.CurrentMember.Properties("Store Sqft"))'

The syntax addition above contains Val, a VBA function that returns numbers contained in a string as a numeric value of appropriate type.

10.  Modify the first line of the SELECT statement as follows:

Step 1 Affected Lines:

   {[Measures].[Store Sales]} ON COLUMNS,

Step 2 Modified Lines:

{[Measures].[Store Sales], [Measures].[Store Size]} ON COLUMNS,

Our query now appears in the Query pane as shown in Illustration 4.

Illustration 4: Step 2, With Additions Noted, as it Appears in the Query Pane

11.  Execute the query by clicking the Run button.

The result set appears partially displayed in Illustration 5.

Illustration 5: Step 2 Partial Results Dataset