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

June 2, 2003

The .PrevMember Function

The .PrevMember function, according to the Analysis Services Books Online, "returns the previous member in the level that contains a specified member." In other words, the function returns the member in the dimensional hierarchy occurring earlier at the same level as the specified member. The utility of the .PrevMember function is obvious when we consider the business need in Accounting and Finance circles to determine change over a given period of time (such as the current year over the prior year, as we will demonstrate in the examples that follow, as well as the current month over the prior month, and so forth).

Discussion:

Keep in mind that Analysis Server traverses the cube structure when we execute our queries, evaluating the measures that exist at each intersect point within that structure. When we use the .PrevMember function, the execution / evaluation process determines the coordinates at which the process "previously" rests, and returns the previous member in the hierarchical level. The time dimension is an excellent instance to use to demonstrate this principle. In fact, using the .CurrentMember and .PrevMember functions together gives us a great illustration of the usefulness of the .PrevMember function.

Syntax

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

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

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


Illustration 4: Example Result Dataset Using the .PrevMember Function

The WITH clause in the example above contains the definition of the calculated member Warehouse Margin PY, my choice for indicating that it is a "prior year" margin figure. The .PrevMember function is used within the definition of the calculated member -- we are simply specifying the previous member of the Time dimension at the year level, which turns out to be 1997, as we have specified 1998 as the slicer in the WHERE clause.

Practice

Let's practice with the .PrevMember function, combining its use with that of the .CurrentMember function, in a manner that assists us in performing analysis of performance from one year to the next. We will rejoin the MDX Sample Application, creating a new query for the purposes of this exercise.

8.             Select File --> New from the top menu of the MDX Sample Application.

9.             Ensure that FoodMart 2000 is selected as the database name in the DB box of the toolbar, and that the Warehouse cube remains selected in the Cube drop-down list box.

10.         Type the following query into the Query pane:

-- MDX08-2:  Tutorial Query No. 2
WITH 
   MEMBER [Measures].[Margin CY] AS
   '([Time].CurrentMember , [Measures].[Warehouse Sales])-([Time].CurrentMember, 
      [Measures].[Warehouse Cost])'
   MEMBER [Measures].[Margin PY] AS
   '([Time].PrevMember , [Measures].[Warehouse Sales])-([Time].PrevMember,  
       [Measures].[Warehouse Cost])'
   MEMBER [Measures].[$ Change] AS
   '([Time].CurrentMember , [Measures].[Margin CY])-([Time].CurrentMember, 
       [Measures].[Margin PY])'

SELECT
   { [Measures].[Margin PY],  [Measures].[Margin CY], [Measures].[$ Change]} ON COLUMNS,
   {([Warehouse].[Country].[USA])} ON ROWS

FROM Warehouse

WHERE ([Time].[1998])

11.         Select Query --> Run from the top menu to execute the query.

The query runs and the result dataset appear, as shown in Illustration 5.


Illustration 5: The Query Result Dataset

The query delivers the results we have requested; we see the three related calculated measures, Margin PY, Margin CY, and $ Change; the latter, $ Change, is a calculated measure that is made up of the subtraction of one of the first two calculated measures from the other. We see that Margin has decreased by $2,013.80 between 1997 and 1998.

Now let's develop our query a step further, while taking up the last of the functions we have targeted for this lesson, .NextMember.








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers