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 Dec 2, 2002

Introducing the SQL Server 'MDX in Analysis Services' Series - Page 8

By William Pearson


More on MDX Expressions

As we discussed earlier, CurrentMember is an MDX function that returns a member. Just as we can use the CurrentMember function to, in turn, retrieve properties of the associated Member, we can also use it indirectly to return information about other members. We can take advantage of member functions to locate and return members that exist at relative positions to the Current Member, or at specific levels of the hierarchy, with the Current Member as a defined starting point.

Use a Member as a Starting Point

To illustrate, we will utilize the Parent function to retrieve the Parent of the Current Member. Leaving the Store dimension where we placed it for the last exercise, we do this by taking the following steps:

  1. Close the lower levels of the Store dimension by double-clicking the "Store Country" heading.
  2. Select the Value property of the MyCalcMem Calculated Member, and click the ellipses button once again.
  3. Once the Calculated Member Builder appears, clear the contents of the Value Expression box.
  4. Expand the String folder in the Functions tree.
  5. Select the Name-member function inside the folder.
  6. In the Value Expression box, select/highlight the <<Member>> token.
  7. Expand the Member folder in the Functions tree.
  8. Double click the Parent function within the Members folder.
  9. Click the <<Member>> token, and then replace it with CurrentMember by double-clicking.
  10. Click/highlight the <<Dimension>> token.
  11. Double-click the Store dimension in the Data tree to replace the <<Dimension>> token.

Our expression should resemble that shown below.

Store.CurrentMember.Parent.Name

  1. Once we have inspected the new expression, we click OK. Our results at this point should replicate those shown below.



Illustration 25: Partial Result Set showing the Parent Name for the Top Level in the Store Dimension ("All Stores")


  1. If we expand the Store Country level by double-clicking, we see that the new Parent of the Current Member (Store Country) is displaying in the MyCalcMem column. This is reflected in Illustration 26.



Illustration 26: The Parent function adjusts to display the Parent in the Drill Down


The Ancestor function is very similar to Parent, except that it to be used to display the "ancestor" whose number of levels away is specified by the user. Parent is a simplified version of this, the same results for which might be obtained by creating an Ancestor function whose position is a single level away. Functions exist for Descendants, Siblings, and others, as well. The Analysis Services Books Online provide in-depth information surrounding these and other functions, and their respective uses.


Page 9: Perform Conditional Tests and Comparisons


See All Articles by Columnist William E. Pearson, III




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


















Thanks for your registration, follow us on our social networks to keep up-to-date