Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Nov 25, 2003

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

By William Pearson

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

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM