MDX Essentials: MDX Member Functions: "Relative" Member Functions - Page 3

The .CurrentMember Function

The .CurrentMember function, according to the Analysis Services Books Online, "returns the current member along a dimension during an iteration." In other words, the function references the current member in use in the cube. The axis of the query that we are constructing provides the context within which "current" has meaning, and within which our calculation is in operation. While we can apply .CurrentMember as we feel it useful, it is important to remember that .CurrentMember is actually the default operator, and its specification is, therefore, optional.

Discussion:

Analysis Server traverses the cube structure when we execute our queries, evaluating the measures that exist at each intersect point within that structure. At any given point in the execution / evaluation process, the coordinates at which the process "currently" rests is made up of each dimension in the cube and a current member (dimension1.CurrentMember, dimension2.CurrentMember, dimension3.CurrentMember, and so on). The .CurrentMember function can be used in many ways, from simple default instances to sophisticated and powerful applications.

Syntax

Let's look at an example to familiarize ourselves with the syntax.

	WITH 
	   MEMBER [Measures].[Warehouse Margin] AS
	   '([Time].CurrentMember , [Measures].[Warehouse Sales])-   
	       ([Time].Currentmember, [Measures].[Warehouse Cost])'
	
	SELECT
	
	   {(Time.CurrentMember)} ON COLUMNS,
	
	   {([Warehouse].[Country].USA)} ON ROWS
	
	FROM Warehouse
	
	WHERE ([Measures].[Warehouse Margin])

The result dataset returned would appear as shown in Illustration 2 below:

In the example above, the WITH section again contains the definition of the calculated member Warehouse Margin. We note, as well, that the .CurrentMember function is used within the definition of the calculated member -- we are simply specifying the current member of the Time dimension, which turns out to be 1997. Indeed, we get the same result set if we omit the [Time].CurrentMember specification from the AS portion of the above example, where we are defining the calculated member. This is because .CurrentMember is the default for any dimensions that we do not explicitly specify; in effect, all unspecified dimensions in the example would also be defaulted to current member. We have, therefore, really been using .CurrentMember all along, perhaps without even knowing it!

Practice

Let's begin the hands-on portion of the lesson by creating a calculated member to reinforce our understanding of the basics we have covered so far, and by using the .CurrentMember function in a manner that assists our ends. The MDX Sample Application (see the second article in our series, Structure of the MDX Data Model, for more information about the Sample Application) will, as before, be our tool for constructing and executing the MDX we examine, and for viewing the result datasets we obtain.

We will begin with a simple illustration that creates a calculated member and uses the .CurrentMember function: our query will expand the scope of the example shown above, to present a result set comprising the calculated member and the two measures from which it is derived, this time for the year 1998.

-- MDX08-1:  Tutorial Query No. 1
WITH 
   MEMBER [Measures].[Warehouse Margin] AS
   '([Time].CurrentMember , [Measures].[Warehouse Sales])-([Time].Currentmember,    
       [Measures].[Warehouse Cost])'

SELECT

   {[Measures].[Warehouse Sales],[Measures].[Warehouse Cost],[Measures].[Warehouse Margin]} 
        ON COLUMNS,
   {([Warehouse].[Country].[USA])} ON ROWS
FROM Warehouse
WHERE ([Time].[1998])

We see the result dataset in Illustration 3, which appears as soon as the specified cells in the Results pane are filled by Analysis Services.

The query delivers the results that we requested; we see the three related measures, total Warehouse Sales, Warehouse Cost, and Warehouse Margin, side-by-side, so we can do a quick visual verification of the effectiveness of the calculation. The total margin for the USA warehouses (or profit before selling, administrative and other expenses, in accounting lingo) for 1998 is shown; we specified 1998 as our slicer dimension in the WHERE clause. (See Lesson Five, MDX Member Functions: The "Family" Functions, for more information on the WHERE clause.) The calculation (defined in our calculated member Warehouse Margin) is applied to each of the dimensions successively, with 1998 specified as the "current" member of the Time dimension.

Now let's develop our query a step further, while taking up the next function we have slated for this lesson, .PrevMember.