Introducing the SQL Server 'MDX in Analysis Services' Series - Page 5
December 2, 2002
"Getting Familiar with the Membership" with MDX Expressions
MDX expressions can be used to retrieve information about members. The functions used can easily be added to our expressions from the Calculated Member Builder. As we've seen, expressions can return strings or numbers. We will explore the use of MDX to display member-related information; our exercises will demonstrate the concepts behind the workings of MDX.
The Functions tree of the Calculated Member Builder contains a list of many functions that are available within MDX. We can view the types of functions as the folders that compose the tree. Each folder name reflects the Category of return values (the types of values returned by the functions within the respective folder).
To illustrate, take the following steps:
We can select functions to build our expressions in the Value Expression box. Functions are added to the Value Expression box by dragging the selected function to the box, by selecting the desired function and clicking the Insert button, or by double-clicking the desired function. A partial view of the Member folder within the Functions tree (expanded by clicking the "+" sign to the folder's left) is shown below.
- Select the Value property (go to Properties at the bottom left corner of the Cube Editor) once again for the MyCalcMem Calculated Member.
- Select the ellipses button.
- When the Calculated Member Builder initializes, clear the Value Expression box.
Illustration 11: Partial View of the Functions Tree
We will demonstrate the way we use functions by taking the following steps:
- Expand the String folder in the Functions tree
- Double-click the UniqueName--Member function
- Click the <<Member>> token (a placeholder) in the Value Expression box, highlighting it, so that the Calculation Member Builder dialog looks like the illustration below.
Illustration 12: Highlight the <<Member>> Token in the Value Expression Box
As we are working with members, we now go to the Member folder within the Functions tree and expand it. (We can collapse the String folder by simply clicking the "-" sign to its left, to free up real estate).
- Expand the Member folder in the Functions tree.
- Double click the CurrentMember function.
A <<Dimension>> token appears in front of our expression in the Value Expression box, as shown below.
Illustration 13: The Value Expression Box, after adding the CurrentMember Function
- Click the <<Dimension>> token to highlight it, then double-click Product in the Data tree (to the left of the Functions tree) to change the expression to:
- Click OK.
Page 6: MDX Expressions Continued
See All Articles by Columnist William E. Pearson, III