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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Nov 24, 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:

WITH 
 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

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




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