posted 2/21/2010 by AdamJorgensen - Views: [820]
There are a large number of Member functions in MDX that do everything from returning ancestry of a member to identifying the current member context dynamically. They can also provide some functionality around periods for opening and closing. Let’s review a few of the most important and commonly reviewed ones here AncestorThis retrieves the ancestors at a specific level. For example if you have a product dimension that has a categories hierarchy that contains two levels: Product and Product Category. You need to specify the hierarchy as well as the level name.This code sample will return the ancestor of "Huffy Blue 1023 Bike" at the Product Level, which will return ‘Street Bike’.ANCESTOR([product].[product categories].[product name].[Huffy Blue 1023 Bike], [product].[product categories].[product category])You can test this with a simple select: ( This will return the default measure for the cube since no measure is specified)SELECT {ANCESTOR([product].[product categories].[product name].[Huffy Blue 1023 Bike], [product].[product categories].[product category])} ON COLUMNS From [Adventure Works]LastChildLastChild returns the last child of a particular member. The following expression returns December 2001 since it is the last child of Q4, 2001[date].[calendar].[cy 2001].[h2 cy 2001].[q4 CY 2001].LastChildThe following returns the last child of the product model lines hierarchy.[product].[product model lines].LastChildMembersThe Members function returns a set of members within a specified dimension and if applicable, hierarchy. If a dimension has more than one hierarchy, you need to specify the hierarchy you want the members from. This example returns all the members from the fiscal calendar hierarchy from the date dimension:[date].[fiscal calendar].membersParentParent will return the parent of the current member. For instance if you have a product hierarchy that has the following: Bikes > Mountain Bikes > Huffy Mountain Special 1023, then the following expression will return ‘Mountain Bikes’[product].[product categories].[model name].[ Huffy Mountain Special 1023].parentThere are many more functions to have fun with and apply to your particular applications, so get out there and start having some fun with MDX !
There are a large number of Member functions in MDX that do everything from returning ancestry of a member to identifying the current member context dynamically. They can also provide some functionality around periods for opening and closing. Let’s review a few of the most important and commonly reviewed ones here
Ancestor
This retrieves the ancestors at a specific level. For example if you have a product dimension that has a categories hierarchy that contains two levels: Product and Product Category. You need to specify the hierarchy as well as the level name.
This code sample will return the ancestor of "Huffy Blue 1023 Bike" at the Product Level, which will return ‘Street Bike’.
ANCESTOR([product].[product categories].[product name].[Huffy Blue 1023 Bike], [product].[product categories].[product category])
You can test this with a simple select: ( This will return the default measure for the cube since no measure is specified)
SELECT {
ANCESTOR([product].[product categories].[product name].[Huffy Blue 1023 Bike], [product].[product categories].[product category])} ON COLUMNS From [Adventure Works]
LastChild
LastChild returns the last child of a particular member. The following expression returns December 2001 since it is the last child of Q4, 2001
[date].[calendar].[cy 2001].[h2 cy 2001].[q4 CY 2001].LastChild
The following returns the last child of the product model lines hierarchy.
[product].[product model lines].LastChild
Members
The Members function returns a set of members within a specified dimension and if applicable, hierarchy. If a dimension has more than one hierarchy, you need to specify the hierarchy you want the members from. This example returns all the members from the fiscal calendar hierarchy from the date dimension:
[date].[fiscal calendar].members
Parent
Parent will return the parent of the current member. For instance if you have a product hierarchy that has the following: Bikes > Mountain Bikes > Huffy Mountain Special 1023, then the following expression will return ‘Mountain Bikes’
[product].[product categories].[model name].[ Huffy Mountain Special 1023].parent
There are many more functions to have fun with and apply to your particular applications, so get out there and start having some fun with MDX !