In this level, we will perform an introductory overview of three highly useful “relative” member functions. Expanding upon our earlier exposure to the powerful member functions, we will extend our exploration to the .CurrentMember, .PrevMember and .NextMember functions. As a part of our examination of these functions, we will focus upon calculated members briefly, in a more formal manner than in earlier steps, as an avenue to demonstrating the use of the “relative” member functions. We will take up calculated members in far more detail as the series progresses, but, for now, we will preview their characteristics and dive right into using them.
In the last several levels of this series, we worked with member “family” functions. These allowed us to travel the dimensional hierarchy, as we witnessed in numerous examples, based upon the relative position of a given “family” member to the member matching the hierarchical relationship specified in the function. In this step, we will examine other, far more common “relative” member functions: .CurrentMember, .PrevMember and .NextMember.
We have encountered calculated members already within the series, using them mainly as a vehicle to introduce the MDX function to which we were directing attention at the given time. Let’s place a little formal focus upon calculated members, for starters, and begin our examination of the “relative” member functions thereafter.
Calculated Members
Calculated members allow us to define new members, based upon dimensions or measures that exist within the cube in which we create the calculated member. They are members whose values depend upon an expression rather than a value within a cube. The potential uses of calculated members are limited only by the imagination and experience of the developer. An example might be the somewhat common business concept of a variance measure. We could create this measure using, say, an actual sales measure and a budgeted sales measure that exist in a given cube.
To create a calculated measure, at least within the context of our simple overview, we will use the WITH clause. The syntax would resemble the following:
WITH MEMBER dimension.name AS ‘Expression’
Dimension represents the dimension into which we are creating the new member. Name is my shorthand for the name of the member we are creating, and expression is the expression from which it is created.
Let’s look at a simple example to gain a feel for the syntax.
WITH
MEMBER
[Measures].[Gross Profit Margin]
AS
(
[Measures].[Internet Sales Amount]
-
[Measures].[Internet Total Product Cost]
)
/
[Measures].[Internet Sales Amount]
, FORMAT_STRING = "Percent"
SELECT
{
[Customer].[Customer Geography].[Country].&[United States]
} ON AXIS(0),
{
[Date].[Calendar].[Calendar Quarter].MEMBERS
} ON AXIS(1)
FROM
[Adventure Works]
WHERE
([Measures].[Gross Profit Margin])In the above example, I include an MDX query, beginning with the SELECT statement, immediately below the WITH clause, to illustrate how I create the calculated member and then “call” it with an immediately subsequent query – we supply both parts together to define, and then return, the desired results. We might, of course, have created the expression composing the calculated member within the Analysis Services Cube Designer, and then simply called it via the SELECT statement as above, but the objective here is to focus upon creation via WITH.
The result dataset returned would appear as partially shown:
Illustration 1: Results Dataset Bearing the Calculated Member (Partial View)
As we can see above, the WITH clause contains the definition of the calculated member Gross Profit Margin. The newly created Gross Profit Margin is a member of the Measures dimension, and is composed of the difference between Internet Sales Amount and Internet Total Product Cost, divided by the Internet Sales Amount. We intersect the United States country member of the Customer.Customer Geography hierarchy with the Calendar Quarters of the Date.Calendar hierarchy for the purposes of our query. The returned dataset, therefore, comprises the percentage of total sales generated by the total Internet Sales Amounts less the Total Product Cost for products, for each of the respective calendar quarters stored within the Adventure Works sample cube.
The SELECT keyword comes after the WITH section, where it begins the specification of the axes / cells to which we have become accustomed in previous levels of this series. The SELECT statement allows us to place the members we wish to display upon the respective axes of the display, including enumeration of the quarters through our use of the .Members function. The WHERE clause, as we know from previous levels, acts as the slicer dimension, which, in the immediate example, allows us to hold the new Gross Profit Margin member constant for purposes of our query results.
We will explore many aspects of calculated members as we proceed through the Stairway to MDX series. This level will use them in the practice examples for purposes of illustrating the use of the “relative” functions that we have targeted for the article; in this way, we will continue to stay on track with our examination of the member functions while becoming comfortable with the rudiments of calculated member usage and behavior. Calculated members will be with us for a long time to come, and will provide many uses and topics of discussion.
The .CurrentMember Function
The .CurrentMember function, according to the Analysis Services Books Online, “returns the current member along a specified dimension or hierarchy during 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.
As noted in the Books Online, when a dimension contains only a single visible hierarchy, the hierarchy can be referred to either by the dimension name or by the hierarchy name, because the dimension name is resolved to its only visible hierarchy. For example, because it resolves to the sole hierarchy in the Measures dimension, Measures.CurrentMember is a valid MDX expression.
Putting .CurrentMember to work is straightforward. When we iterate through a set of hierarchy members, at any given step in the iteration, the member being operated upon is the “current” member. When using the .CurrentMember function to return that member, we simply append it to the right of the hierarchy expression. The general syntax is shown in the following string:
<<Hierarchy_Expression >>.CurrentMember
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 hierarchy in the cube and a current member (dimension.hierarchy1.CurrentMember, dimension.hierarchy2.CurrentMember, dimension.hierarchy3.CurrentMember, and so on). The .CurrentMember function can be used in many ways, from simple default instances to sophisticated and powerful applications. Those who work regularly with MDX will find .CurrentMember to be, easily, one of the most pervasive functions.
Let’s begin our hands-on exposure to .CurrentMember, within a calculated member, by replicating the query above, but this time with the addition of the .CurrentMember function within the specification of the Date dimension member in the tuples involved. We will, as usual, work with the MDX Query Editor in SSMS. If you are not sure how to set up SSMS in Analysis Services to access the Adventure Works DW 2008R2 database, then click here for guidance.
- Click / select the Adventure Works cube in the SSMS Object Explorer.
- Click the New Query button above the Object Explorer.
- Type (or copy and paste) the following query onto the new canvas of the Query pane that opens:
-- SMDX010-001 Use of .CurrentMember function in the specification of Date dimension member in tuples comprising a calculated member WITH MEMBER [Measures].[Gross Profit Margin] AS (([Date].[Calendar].CURRENTMEMBER, [Measures].[Internet Sales Amount]) - ([Date].[Calendar].CURRENTMEMBER, [Measures].[Internet Total Product Cost] )) / ([Date].[Calendar].CURRENTMEMBER,[Measures].[Internet Sales Amount]) , FORMAT_STRING = "Percent" SELECT {([Customer].[Customer Geography].[Country].&[United States])} ON AXIS(0), {([Date].[Calendar].[Calendar Quarter].MEMBERS)} ON AXIS(1) FROM [Adventure Works] WHERE ([Measures].[Gross Profit Margin])
The Query pane appears, with our input.
Illustration 2: Our Initial Query, Employing .CurrentMember, in the Query Pane …
- Click the Execute (!) button in the toolbar.
The Results pane is populated by Analysis Services, and the dataset appears, as partially shown.
Illustration 3: Example Result Dataset Using the .CurrentMember Function (Partial View)
In the example above, the WITH section again contains the definition of the calculated member Gross Profit Margin. We note, as well, that the .CurrentMember function is employed within the definition of the calculated member – we are simply specifying the current member of the Date dimension (Calendar hierarchy), which turns out to be the calendar quarter specified in the respective row of the Rows axis. Indeed, we note that we get the same result set if we omit the [Date].[Calendar].CurrentMember specification from the AS portion of the above example, as we did in the introductory example just above, 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!
- Save the query by selecting File Save MDXQuery1.mdx As …, naming the file SMDX010-001.mdx, and placing it in a meaningful location.
Let’s get some more hands-on exposure with .CurrentMember, this time in a fresh application of what we have learned. Let’s say that a representative of our hypothetical client, Adventure Works Cycles, approaches us with a relatively straightforward requirement: For a diagram they wish to create, somewhat similar to a chart of accounts, the client has a need for a list of all Product Model Line members together with the immediate, respective “rollup” parents, to document the hierarchy, as well as to serve as the basis for logic that will be used in allocation and other efforts.
We see this as an opportunity to demonstrate the utility of MDX, and to specifically put the .CurrentMember function to work. In this case, we will see how we can even “extend” .CurrentMember with additional functions that we can affix to it. We will return to the SSMS MDX Query Editor and create a new query based upon the connection to the cube that we created earlier.
- From our current position within the Query Editor, select File New from the top menu.
- Select Query with Current Connection from the cascading menu that appears next, as shown.
Illustration 4: Creating a New Query via the Current Connection
- Type (or copy and paste) the following query into the Query pane:
-- SMDX010-002: Basic Use of .CurrentMember (and other functions) within a Calculated Member
WITH
MEMBER
[Measures].[Parent Name]
AS
[Product].[Product Model Lines].CURRENTMEMBER.PARENT.NAME
SELECT
{([Measures].[Parent Name])} ON AXIS(0),
{([Product].[Product Model Lines].ALLMEMBERS)} ON AXIS(1)
FROM
[Adventure Works]The query appears within the Query pane:
Illustration 05: Query Employing .CurrentMember Appears in the Query Pane
- Click the Execute (!) button in the toolbar, once again.
Analysis Services populates the Results pane, as partially shown:
Illustration 06: The Query Results, Delivered via the Combination of .CurrentMember and .Parent (Partial View)
The logic we employ in our calculated member, Parent Name, relies upon .CurrentMember to specify the current member of the Product Model Lines hierarchy of the Product dimension. Based upon the current member, we then access the respective parent member within the same hierarchy through the use of .Parent. (For more information on the .Parent function, see Stairway to MDX - Level 6: Member “Family” Functions: .Parent and .Children.) Finally, we access the Name property of the parent member via the .Name function, affixing it to the end of our three-function string .CURRENTMEMBER.PARENT.NAME. We note that, because the “All” member of the Products dimension has no parent (resting, as it does, atop the hierarchy), a “(null)” is returned.
- Save the query by selecting File Save MDXQuery2.mdx As …, naming the file SMDX010-002.mdx, and placing it, as we did with the query we constructed earlier, in a meaningful location.
Now that we’ve seen, in a general sense, how to use the .CurrentMember functions, let’s take a look at another “relative” member function, .PrevMember. We will also see another appearance of .CurrentMember, when we use the two in combination to achieve a stated business need.
The .PrevMember Function
The .PrevMember function, according to the Analysis Services Books Online, “returns the previous member in the same level as the 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 between adjacent periods 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).
Using the .PrevMember function to achieve desired ends is, much like .CurrentMember, uncomplicated: we simply append it to the right of a member expression. The general syntax is shown in the following string:
<<Member_Expression >>.PrevMember
We noted, in our earlier examination of .CurrentMember, 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” rested, and returns the previous member in the hierarchical level. The Date dimension is an excellent instance to use to demonstrate this principle. Moreover, using the .CurrentMember and .PrevMember functions together gives us a great illustration of the usefulness of the .PrevMember function, as we shall see.
The following example demonstrates a simple use of .PrevMember, within a query, to display the name of the member immediately “before” the current member on the Rows axis:
WITH
MEMBER
[Measures].[PrevDateMember]
AS
[Date].[Fiscal].CURRENTMEMBER.PREVMEMBER.NAME
SELECT
{
[Measures].[PrevDateMember]
} ON AXIS(0),
{
[Date].[Fiscal].MEMBERS
} ON AXIS(1)
FROM
[Adventure Works]The result dataset returned (scrolled to December 26, 2005, to show a representative set of hierarchical levels) would appear as partially shown:
Illustration 07: Example Result Dataset Returned via the Combination of .CurrentMember and .PrevMember (Partial View)
As we noted in our discussion of .CurrentMember earlier, 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 hierarchy in the cube and a current member. The calculated member then employs the .PrevMember function to “look back” a single member for each respective current member, generating the previous member in the hierarchical level.
We’re ready to get some hands-on exposure to .PrevMember, within a calculated member. Let’s say that the representative of Adventure Works Cycles comes to us, once again, with a request that we demonstrate a “quick and dirty” calculation of deltas between the periods occupying the various levels of the Date hierarchy and their respective predecessor periods, for a given year. We are to select calendar year 2005, the beginning year for Adventure Works (we are notified that sales did not begin until the beginning of the second half of the year, July 1, 2005). Our colleague also requests that we suppress nulls, where possible, for periods that are reflected as empty in the Adventure Works cube.
We will return to the SSMS MDX Query Editor and perform the steps that follow:
- From our current position within the Query Editor, select File à New from the top menu.
- Select Query with Current Connection from the cascading menu that appears next.
- Type (or copy and paste) the following query onto the new canvas of the Query pane that opens:
-- SMDX010-003 Use of .PrevMember with .CurrentMember in the calculation of
-- deltas between current and previous periods in the Date.Calendar hierarchy
WITH
MEMBER [Measures].[Internet Sales Growth]
AS
([Date].[Calendar].CURRENTMEMBER, [Measures].[Internet Sales Amount])
-
([Date].[Calendar].PREVMEMBER, [Measures].[Internet Sales Amount])
, FORMAT_STRING = "Currency"
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Internet Sales Growth] }
ON AXIS(0),
NON EMPTY
{DESCENDANTS([Date].[Calendar].[Calendar Year].[CY 2005],
[Date].[Calendar].[Calendar Year].[Quarter], SELF_AND_AFTER)}
ON AXIS(1)
FROM
[Adventure Works]The Query pane appears, with our input.
Illustration 08: Our Query, Employing the .PrevMember / .CurrentMember Combination, in the Query Pane …
Click the Execute (!) button in the toolbar, once again.
The Results pane is populated by Analysis Services, and the dataset appears, as partially shown.
Illustration 09: Example Result Dataset Based upon the .PrevMember / .CurrentMember Combination (Partial View)
In the example above, the WITH clause again contains the definition of the calculated member Internet Sales Growth. We employ a combination of the .CurrentMember and .PrevMember functions to generate the respective “current period” at its given point within the Date.Calendar dimension hierarchy, as well as the corresponding “previous period.” We note that, unsurprisingly, the calculated measure, Internet Sales Growth, has the same value as the base measure, Internet Sales Amount, in each of the members CY 2005, H2 CY 2005, Q3 CY 2005, July 2005 and July 1, 2005 in the Date.Calendar hierarchy levels. As we learned earlier, sales began on July 1, 2005, and, so, the values at each of these levels reflect the full delta over the (empty) previous periods.
- Save the query by selecting File Save MDXQuery3.mdx As …, naming the file SMDX010-003.mdx, and placing it with the queries we constructed earlier in this article or elsewhere.
Let’s get some more exposure to .PrevMember, this time to create a calculation that we will subsequently subject to a filter. We’ll say, this time, that our client colleague has returned with a request, now that we have generated a list of deltas between current and previous periods, that we generate a list of “outliers” from the perspective of those deltas. He particularly wants a demonstration of how we might approach generating a list restricted to the periods with deltas whose magnitudes fall into a band that is considered “less than desirable growth.” Specifically, for the purposes of our demonstration, he wants us to generate a simple list of operating months with a delta of less than $ 10,000 growth over the prior month.
We will approach this in two steps, in hopes of making it a bit easier for our colleague to digest.
- From our current position within the Query Editor, select File New from the top menu.
- Select Query with Current Connection from the cascading menu that appears next, once again.
- Type (or copy and paste) the following query onto the new canvas of the Query pane that opens:
-- SMDX010-004-1 Use of .PrevMember with .CurrentMember in the calculation of
-- a list of periods with "deltas to investigate further" (Initial Setup)
WITH
MEMBER [Measures].[Internet Sales Amount - Prev Mo.]
AS
([Date].[Calendar].CURRENTMEMBER.PREVMEMBER, [Measures].[Internet Sales Amount])
, FORMAT_STRING = "Currency"
MEMBER [Measures].[Internet Sales Amount - Delta]
AS
([Date].[Calendar].CURRENTMEMBER, [Measures].[Internet Sales Amount])
-
([Date].[Calendar].PREVMEMBER, [Measures].[Internet Sales Amount])
, FORMAT_STRING = "Currency"
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Internet Sales Amount - Prev Mo.],
[Measures].[Internet Sales Amount - Delta] }
ON AXIS(0),
NON EMPTY
{[Date].[Calendar].[Month].MEMBERS}
ON AXIS(1)
FROM
[Adventure Works]The Query pane appears, with our input.
Illustration 10: First Step of Generating an Outliers List: Monthly Deltas
- Click the Execute (!) button in the toolbar, as before.
The Results pane is populated by Analysis Services, and the dataset appears once again, as partially shown.
Illustration 11: “Preparatory Result Dataset” Each Month’s Delta vs. Previous Month (Partial View)
Now that we have calculated the logic to generate the deltas between each month and its predecessor, we are ready to generate a list of “outlier” months that warrant further investigation in a “management by exception” scenario. To attain this objective, we need to restrict the returned dataset to the member months whose deltas over their immediate predecessors were less than $ 10,000.
- Save the query by selecting File Save MDXQuery4.mdx As …, naming the file SMDX010-004-1.mdx, and placing it with the queries we constructed earlier.
- Re-save the file as SMDX010-004-2.mdx, leaving the new file open in the Query Editor.
We will next modify the query in accordance with the following steps:
- Replace the existing “comments lines,” which appear as follows:
-- SMDX010-004-1 Use of .PrevMember with .CurrentMember in the calculation of -- a list of periods with "deltas to investigate further” (Initial Setup)
… with these commented lines:
-- SMDX010-004-2 Use of .PrevMember with .CurrentMember in the calculation of a list of periods -- with "deltas to investigate further" (Add filtering logic to present only specified outliers)
- Within the SELECT clause, modify the existing Row / Axis(1) specification from the existing syntax:
NON EMPTY
{[Date].[Calendar].[Month].MEMBERS}
ON AXIS(1)… to the following syntax:
NON EMPTY
{
FILTER(
[Date].[Calendar].[Month].MEMBERS
, [Internet Sales Amount - Delta]< 10000
)
} ON AXIS(1)
The complete query should appear, with modifications, as follows:
-- SMDX010-004-2 Use of .PrevMember with .CurrentMember in the calculation of a list of periods
-- with "deltas to investigate further" (Add filtering logic to present only specified outliers)
WITH
MEMBER [Measures].[Internet Sales Amount - Prev Mo.]
AS
([Date].[Calendar].CURRENTMEMBER.PREVMEMBER, [Measures].[Internet Sales Amount])
, FORMAT_STRING = "Currency"
MEMBER [Measures].[Internet Sales Amount - Delta]
AS
([Date].[Calendar].CURRENTMEMBER, [Measures].[Internet Sales Amount])
-
([Date].[Calendar].PREVMEMBER, [Measures].[Internet Sales Amount])
, FORMAT_STRING = "Currency"
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Internet Sales Amount - Prev Mo.],
[Measures].[Internet Sales Amount - Delta] }
ON AXIS(0),
NON EMPTY
{
FILTER(
[Date].[Calendar].[Month].MEMBERS
, [Internet Sales Amount - Delta]< 10000
)
} ON AXIS(1)
FROM
[Adventure Works]We have simply changed the Row specification to return months whose deltas over previous months reflect values less than $ 10,000.
The Query pane appears, with our modifications in place.
Illustration 12: Second Step of Generating an Outliers List: Applying Required Filter
- Click the Execute (!) button in the toolbar, once again.
Analysis Services populates the Results pane, and the much smaller dataset appears, as shown.
Illustration 13: Final Result Dataset: List of Months with Outlier Deltas over Previous Months
We obtain confirmation from our client colleague that the list of “outliers” that we have provided meets the immediate need – especially when we show how easily the dollar threshold for list inclusion can be parameterized in the reporting layer.
- Save the query by selecting File Save MDXQuery5.mdx As …, naming the file SMDX010-004-2.mdx, and placing it with the queries we constructed earlier in this article or elsewhere.
Having gained some exposure to the .CurrentMember function, as well as a means of “stepping back a member” via the .PrevMember function, let’s explore another relative member function, .NextMember.
The .NextMember Function
According to the Analysis Services Books Online, the .NextMember function “returns the next member in the level that contains a specified member.” In other words, the function returns the member in the dimensional hierarchy occurring “one beyond,” or “one later,” at the same level as the specified member. The .NextMember function works in a manner similar to the .PrevMember function, except that, instead of returning the member that occurs at the same level as the specified member, earlier in the hierarchy, it returns the same-level member that follows the specified member within the hierarchy. Much as we saw to be the case with .PrevMember, the function is particularly useful in Accounting and Finance scenarios, where change between adjacent time periods is a relatively common business consideration.
Using the .NextMember function to achieve desired ends is, like .CurrentMember and .PrevMember, straightforward: we simply append it to the right of a member expression. The general syntax is shown in the following string:
<<Member_Expression >>.NextMember
We noted, in our earlier examination of .CurrentMember, 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 .NextMember function, the execution / evaluation process determines the coordinates at which the process “next” in the process rests, and returns the next member in the hierarchical level. The Date dimension is, once again, an excellent instance to use to demonstrate this principle. In fact, just as we have seen to be the case with .CurrentMember and .PrevMember, using the .CurrentMember and .NextMember functions in combination gives us a great illustration of the usefulness of the .NextMember function, as we shall see.
The following example demonstrates a simple use of .NextMember, within a query, to display the name of the member immediately after the current member on the Rows axis:
WITH
MEMBER
[Measures].[NextDateMember]
AS
[Date].[Fiscal].CURRENTMEMBER.NEXTMEMBER.NAME
SELECT
{
[Measures].[NextDateMember]
} ON AXIS(0),
{
[Date].[Fiscal].MEMBERS
} ON AXIS(1)
FROM
[Adventure Works]The result dataset returned (scrolled to December 26, 2005, to show a representative set of hierarchical levels) would appear as partially shown:
Illustration 14: Example Result Dataset Returned via the Combination of .CurrentMember and .NextMember (Partial View)
As we noted in our discussion of .CurrentMember, as well as .PrevMember, earlier, 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 hierarchy in the cube and a current member. The calculated member then employs the .NextMember function to “look forward” a single member for each respective current member, generating the next member in the hierarchical level.
Let’s get some hands-on exposure to .NextMember, again via a calculated member. Let’s assume, again, that the Adventure Works representative comes to us with a request that we demonstrate a straightforward calculation of deltas between the periods occupying the various levels of the Date.Calendar hierarchy and their respective following periods, for a given year. We are to select calendar year 2005, as we did in the earlier exercise for .PrevMember, the beginning year for Adventure Works (we are notified that sales did not begin until the beginning of the second half of the year, July 1, 2005). Our colleague also requests that we suppress nulls, where possible, for periods that are reflected as empty in the Adventure Works cube. (For purposes of our practice queries with .NextMember, we will “reverse” time, and subtract the current month from the next month, rather than going in the direction we did with the .PrevMember examples, keeping in mind that this can certainly be done in either direction.)
We will return to the SSMS MDX Query Editor and perform the steps that follow:
- From our current position within the Query Editor, select File New from the top menu.
- Select Query with Current Connection from the cascading menu that appears next.
- Type (or copy and paste) the following query onto the new canvas of the Query pane that opens:
-- SMDX010-005 Use of .NextMember with .CurrentMember in the calculation of
-- deltas between next and current periods in the Date.Calendar hierarchy
WITH
MEMBER [Measures].[Internet Sales Growth]
AS
([Date].[Calendar].NEXTMEMBER, [Measures].[Internet Sales Amount])
-
([Date].[Calendar].CURRENTMEMBER, [Measures].[Internet Sales Amount])
, FORMAT_STRING = "Currency"
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Internet Sales Growth] }
ON AXIS(0),
NON EMPTY
{DESCENDANTS([Date].[Calendar].[Calendar Year].[CY 2005],
[Date].[Calendar].[Calendar Year].[Quarter], SELF_AND_AFTER)}
ON AXIS(1)
FROM
[Adventure Works]The Query pane appears, with our input.
Illustration 15: Our Query, Employing the .NextMember / .CurrentMember Combination, in the Query Pane …
- Click the Execute (!) button in the toolbar, once again.
The Results pane is populated by Analysis Services, and the dataset appears, as partially shown.
Illustration 16: Example Result Dataset Based upon the .NextMember / .CurrentMember Combination (Partial View)
The example above accomplishes very similar results to those of the first practice example we undertook for the .PrevMember function earlier. The WITH section contains the definition of the calculated member Internet Sales Growth, within which we employ a combination of the .NextMember and .CurrentMember functions to generate the respective “current period” at its given point within the Date dimension hierarchy, as well as the corresponding “next period.”
- Save the query by selecting File Save MDXQuery6.mdx As …, naming the file SMDX010-005.mdx, and placing it with the queries we constructed earlier in this article or elsewhere.
We will continue to follow the path we took with .PrevMember earlier, and get some more exposure to .NextMember through the creation of a calculation that we subsequently subject to a filter. We’ll say, this time, that our client colleague has returned with a request, now that we have generated a list of deltas between current and next periods, that we generate a list of “outliers” from the perspective of those deltas. He particularly wants a demonstration of how we might approach generating a list restricted to the periods with deltas whose magnitudes fall into a band that is considered “less than desirable growth.” Specifically, for the purposes of our demonstration, he wants us to generate a simple list of operating months with a delta of less than $ 10,000 growth over the prior month. (As we have already mentioned, for purposes of the query, we will reverse time, and subtract the current month from the next month.)
We will approach this in two steps, once again in hopes of making it a bit easier for our colleague to assimilate.
- From our current position within the Query Editor, select File New from the top menu.
- Select Query with Current Connection from the cascading menu that appears next, once again.
- Type (or copy and paste) the following query onto the new canvas of the Query pane that opens:
-- SMDX010-006-1 Use of .NextMember with .CurrentMember in the calculation of
-- a list of periods with "deltas to investigate further" (Initial Setup)
WITH
MEMBER [Measures].[Internet Sales Amount - Next Mo.]
AS
([Date].[Calendar].CURRENTMEMBER.NEXTMEMBER, [Measures].[Internet Sales Amount])
, FORMAT_STRING = "Currency"
MEMBER [Measures].[Internet Sales Amount - Delta]
AS
([Date].[Calendar].NEXTMEMBER, [Measures].[Internet Sales Amount])
-
([Date].[Calendar].CURRENTMEMBER, [Measures].[Internet Sales Amount])
, FORMAT_STRING = "Currency"
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Internet Sales Amount - Next Mo.],
[Measures].[Internet Sales Amount - Delta] }
ON AXIS(0),
NON EMPTY
{[Date].[Calendar].[Month].MEMBERS}
ON AXIS(1)
FROM
[Adventure Works]The Query pane appears, with our input.
Illustration 17: First Step of Generating an Outliers List: All Month’s Deltas
- Click the Execute (!) button in the toolbar, as before.
The Results pane is populated by Analysis Services, and the dataset appears once again, as partially shown.
Illustration 18: “Preparatory Result Dataset:” Monthly Deltas (Partial View)
Now that we have calculated the logic to generate the deltas between each “next” month and its successor, the current month, we are ready to generate a list of “outlier” months that warrant further investigation in a “management by exception” scenario. To attain this objective, we need to restrict the returned dataset to the member months whose deltas over their immediate predecessors were less than $ 10,000.
- Save the query by selecting File Save MDXQuery7.mdx As …, naming the file SMDX010-006-1.mdx, and placing it with the queries we constructed earlier in this article or elsewhere.
- Re-save the file as SMDX010-006-2.mdx, leaving the new file open in the Query Editor.
We will next modify the query in accordance with the following steps:
- Replace the existing “comments lines,” which appear as follows:
-- SMDX010-006-1 Use of .NextMember with .CurrentMember in the calculation of -- a list of periods with "deltas to investigate further" (Initial Setup)
… with these commented lines:
-- SMDX010-006-2 Use of .NextMember with .CurrentMember in the calculation of a list of periods -- with "deltas to investigate further" (Add filtering logic to present only specified outliers)
- Within the SELECT clause, modify the existing Row / Axis(1) specification from the existing syntax:
NON EMPTY
{[Date].[Calendar].[Month].MEMBERS}
ON AXIS(1)
… to the following syntax:
NON EMPTY
{
FILTER(
[Date].[Calendar].[Month].MEMBERS
, [Internet Sales Amount - Delta]< 10000
)
} ON AXIS(1)The complete query should appear, with modifications, as follows:
-- SMDX010-006-2 Use of .NextMember with .CurrentMember in the calculation of a list --- of periods with "deltas to investigate further" (Add filtering logic to present -- only specified outliers)
WITH
MEMBER [Measures].[Internet Sales Amount - Next Mo.]
AS
([Date].[Calendar].CURRENTMEMBER.NEXTMEMBER, [Measures].[Internet Sales Amount])
, FORMAT_STRING = "Currency"
MEMBER [Measures].[Internet Sales Amount - Delta]
AS
([Date].[Calendar].NEXTMEMBER, [Measures].[Internet Sales Amount])
-
([Date].[Calendar].CURRENTMEMBER, [Measures].[Internet Sales Amount])
, FORMAT_STRING = "Currency"
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Internet Sales Amount - Next Mo.],
[Measures].[Internet Sales Amount - Delta] }
ON AXIS(0),
NON EMPTY
{
FILTER(
[Date].[Calendar].[Month].MEMBERS
, [Internet Sales Amount - Delta]< 10000
)
} ON AXIS(1)
FROM
[Adventure Works]We have simply changed the Row specification to return only months whose deltas over previous months reflect values less than $ 10,000.
The Query pane appears, with our modifications in place.
Illustration 19: Second Step of Generating an Outliers List: Applying Required Filter
- Click the Execute (!) button in the toolbar, once again.
Analysis Services populates the Results pane, and the much smaller dataset appears, as shown.
Illustration 20: Final Result Dataset: List of Months with Outlier Deltas over Previous Months
We obtain confirmation from our client colleague that the list of “outliers” that we have provided meets the immediate need, particularly in the way that it corroborates the results of our .PrevMember practice example earlier. An important attribute of this approach, once again, is the fact that the dollar threshold for list inclusion can be parameterized in the reporting layer.
- Save the query by selecting File Save MDXQuery8.mdx As …, naming the file SMDX010-006-2.mdx, and placing it with the queries we constructed earlier in this article or elsewhere.
- Select File -- Exit to leave the SQL Server Management Studio, when ready.
We will gain a great deal of practical exposure to the .CurrentMember, .PrevMember and .NextMember functions throughout the levels of Stairway to MDX. Indeed, we will find them to be some of the most frequently used functions within our MDX toolkits for as long as we continue to query cubes with the language.
Summary
In this article, we explored three “relative” MDX functions, .CurrentMember, .PrevMember and .NextMember. We noted the ubiquity of these basic, but highly useful, “soldiers” in the day-to-day queries of cubes in the business environment, and concentrated, to some extent, on their combined use to construct a basis upon which we can drive the return and display of a gamut of values and calculations. In introducing each function, we commented upon its operation and touched upon examples of the effects we can deliver through its use.
We examined the syntax involved with each of the .CurrentMember, .PrevMember and .NextMember functions, discussing some business uses for each and illustrating its use within an example query that capitalized upon its respective capabilities. We gained the know-how needed to take advantage of these useful functions through the typical Stairway to MDX practice exercises, whereby we constructed queries that used each function, and then examined the results datasets those queries delivered.