Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
More on MDX Expressions
As discussed earlier, CurrentMember is an MDX function that returns a member. Just as we can use the CurrentMember function to retrieve properties of the associated member, we can 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.
Using a Member as a Starting Point
To illustrate, we'll use 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:
Close the lower levels of the Store dimension by double-clicking the Store Country heading.
Select the Value property of the MyCalcMem calculated member and then click the ellipsis (...) button.
When the Calculated Member Builder appears, clear the contents of the Value Expression box.
Expand the String folder in the Functions tree.
Select the Name-member function inside the folder.
In the Value Expression box, select the <<Member>> token.
Expand the Member folder in the Functions tree.
Double-click the Parent function within the Members folder.
Click the <<Member>> token, and then replace it with CurrentMember by double-clicking.
Select the <<Dimension>> token.
Double-click the Store dimension in the data tree to replace the <<Dimension>> token.
The expression should resemble the one below.
Store.CurrentMember.Parent.Name
Click OK. The results should resemble Figure 25.
Figure 25 Partial result set showing the parent name for the top level in
the Store dimension (All Stores).Expand the Store Country level by double-clicking. The new parent of the current member (Store Country) is displayed in the MyCalcMem column (see Figure 26).
Figure 26 The Parent function adjusts to display the parent in
the drill-down.
)
Figure 25)
Figure 26The Ancestor function is very similar to Parent, except that it's used to display the "ancestor" whose number of levels away is specified by the user. Parent is a simplified version of this function; you can obtain the same results by creating an Ancestor function whose position is a single level away. Functions also exist for descendants, siblings, and others. The Analysis Services Books Online provide in-depth information about these and other functions.
Performing Conditional Tests and Comparisons
We'll conclude this tutorial with a review of an expression that contains conditional testing, coupled with a comparison test, to illustrate these important concepts.
Swap the Product dimension with the Store dimension, so that the Product dimension returns to the row axis. The Store dimension once again returns to the top portion of the Cube Editor.
Select the Value property of the MyCalcMem calculated member.
Click the ellipsis (...) button. In the Value Expression box, modify the expression to look like the one below:
Ancestor([Product].CurrentMember,[Product].[Product Category]).Name = "Bread"
Click OK. The data returned should appear as partially shown in Figure 27, after drilling down past the Bread product category. (The drill-down path is Food, Baked Goods, Bread, and so forth.)
Figure 27 The Results of the Application of a Comparison.
)
Figure 27The MyCalcMem member contains 1 (equivalent to true) or 0 (false) with regard to the respective product's membership in the Bread category.
This presents an opportunity to introduce a conditional test within the IIF function, which allows us to display various values based on conditional test results. We'll modify our expression once again to include logic that assigns a specific statement to the current ones and zeros, providing data that might be more intuitive for information consumers.
To begin, we'll establish three arguments, the first of which is used by the IIF function as a conditional test (a test whose result is either 1 or 0, denoting true or false, respectively). The next argument provides the value assigned by the function if the conditional test results in a true scenario. The value assigned can be a number or string; we'll assign it the term Restrict. (For this example, we want to create a list of bread-laden foods to provide to customers who want to know which products are not suggested for gluten-intolerant individuals.)
Finally, the third argument assigns a value for any data that evaluates as false in the conditional test of the first argument. In our example, these products will be classified as unrestricted with regard to consumption by gluten-intolerant individuals.
In this simplified example, we'll take as a general rule any product that belongs to the Bread category to be a "gluten watch" item. We'll assume that any product for which Bread is not an assigned category name is unrestricted. To summarize, we're going to do the following:
Perform a conditional test (via the argument found in the IIF function) to ascertain whether the product category equals Bread.
If the product category is Bread, place the term "Gluten-Watch" in the MyCalcMem column. If the word Bread doesn't appear as the name of the product category, assign the member under examination an unrestricted status.
To achieve our objectives, we perform the following steps:
Clear the Value Expression box for the MyCalcMem calculated member.
Type the following into the Value Expression box:
IIF(Ancestor([Product].CurrentMember,[Product].
[Product Category]).Name = "Bread","Gluten-Watch","Unrestricted")Click OK and then review the values that are returned. (Figure 28 shows partial results.)
Figure 28 Assigning products to reporting classes based on conditional
tests.Now suppose that want to increase the scope of our nutritional list to further include the classification of products containing eggs, which might be bad news for people who suffer adverse reactions from various components found in eggs. The use of the OR statement results in the return of 1 (true) if either of the two statements joined by the OR is true. Conversely, AND requires both to be true to return 1 (true). We want to build in flexibility here, and simply label the potentially restricted products as being under "Intolerance Watch," to give us the capability of adding other items as more such food groups are inevitably deemed risky for various individuals. Let's continue our example in practical form.
Select the Value property and click the ellipsis (...) button.
In the Value Expression box, clear the expression and then enter the following expression:
IIF(Ancestor([Product].CurrentMember,[Product].[ProductCategory]).Name = "Bread" OR Ancestor([Product].CurrentMember,
[Product].[Product Category]).Name = "Eggs",
"Intolerance Watch", "Unrestricted")Click OK and then review the returned values (see Figure 29).
Figure 29 Extending the expression with the addition of OR to the conditional statement.
)
Figure 28)
Figure 29