Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jun 2, 2003

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

By William Pearson

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.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date