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.
» 
Comment and Contribute
