Introducing the SQL Server 'MDX in Analysis Services' Series - Page 6
December 2, 2002
The results that are returned show the MyCalcMem column to be populated with the Unique Name of the current member, which includes its hierarchy (in brackets, and delimited by single decimals). This represents a sort of "qualified name" whereby no member of a cube is without unique identification.
To repeat in similar fashion, we will perform the same process with the Name-Dimension function.
- Select the Value property (again, bottom left corner of the Cube Editor).
- Click the ellipses button.
- Clear the Value Expression box when the Calculated Member Builder appears.
- Expand the String folder in the Functions tree.
- Double-click the Name-Member function (<<Member>>.Name should appear).
- Click the <<Member>> token in the Value Expression box to highlight it.
- Expand the Member folder in the Functions tree.
- Double-click the CurrentMember function.
Our Value Expression box should display the following expression:
- Click the <<Dimension>> token to highlight it, and then double-click Product in the Data tree.
The expression becomes as shown below:
Illustration 14: The Product Dimension in the Expression
- Click OK, and observe that the MyCalcMem column now lists the Member Name, as partially shown below (exploded view):
Illustration 15: The new MyCalcMem Values, using the Member Name Function
- Now, let's drag the Store dimension from the top pane down to replace the Product dimension in the row axis (you can drop the icon that appears over the "Product Family" headings currently in place - a small, double-headed arrow appears at the "drop" point), "swapping" Product (to the top) with Store (to below, in its old place).
Notice that the context of MyCalcMem has become "for All Products;" if we look above at the (newly positioned) Product dimension, which is now serving as a part of the filter list, we can see it is set to All Products.
- Select Alcoholic Beverages in the Product filter list, by scrolling down the hierarchy that appears, as shown below.
Illustration 16: The Product Hierarchy as an Exploded Selection
The Current Member of the Product dimension is now Alcoholic Beverages, so the MyCalcMem value becomes Alcoholic Beverages. ("Current Member" is the value that appears on the column or row axis, assuming that the dimension itself appears on the column or row axis, respectively - if not, and the dimension appears in the filter section, the Filter box displays the "Current Member.")
- Return the Product dimension to its original position in the row axis, simply "swapping" it again with the Store dimension, which, in turn, returns to the top.
- Double-click the Product Family hierarchy. This exposes the Product Department level to the right of the Product Family level, as partially shown in Illustration 17 below (scroll over to the far right; the row axis remains fixed).
Illustration 17: The Product Department appears as the First Level of Drill Down
Notice that MyCalcMem has assumed the values at the Product Department level (except for rollups/totals, where it displays the Product Family names). The Current Member of a dimension can exist at any level of the hierarchy.
Page 7: Rudimentary Conversion Functions
See All Articles by Columnist William E. Pearson, III