MDX Time Series Functions, Part II: The OpeningPeriod () and ClosingPeriod() Functions - Page 3

August 11, 2003

The ClosingPeriod() Function

The ClosingPeriod() function, according to the Analysis Services Books Online, "returns the last sibling among the descendants of a member at a specified level." In other words, it returns the last member that shares the current member's parent at the specified level.

Identically to the case of the OpeningPeriod () function, when a level is specified, the dimension containing that level is used. When no level is specified, the Time dimension is used. Further, if no level is specified, the level below that of the specified member is used. If neither level nor member is specified in the function, the default is Time.CurrentMember (a concept we have discussed earlier in our series), with the Level value thus being the parent level of Time.CurrentMember.

Discussion

The ClosingPeriod() function allows us to meet similar business needs to those for which the OpeningPeriod() function is so well adapted, including (and especially) those that require that we return a balance from the last member of a given level. To use a similar example to the one we used for OpeningPeriod(), that level might be Year, with the last member being December. A relative calculation can thus be driven for the single last month in the Year level of the Time dimension. While other, less direct approaches exist to meet this requirement, ClosingPeriod() provides an easy route to the "ending period balance" for an account; indeed, this is its most common use.

Syntax

Syntactically, the referenced member is placed within the parentheses to the right of ClosingPeriod, as shown in the following illustration:

ClosingPeriod([<<Level>>[, <<Member>>]])

The function returns the last member within the <<Level>>, among the descendants of <<Member>>. The following simple example expression would return December (identified simply as "12" in the Warehouse sample cube) of year 1998 - or, in other words, the last member (Month 12) of the Quarter level in 1998.

ClosingPeriod ([Time].[Quarter], [1998] )

Practice

Let's once again reinforce our understanding of the basics by creating a calculated member within which we will use the ClosingPeriod() function in meeting a business need.

We will begin with an illustration that creates a calculated member and uses the ClosingPeriod() function in a scenario quite similar to that in our OpeningPeriod() function. This will afford us an opportunity to compare closely the results to see the precise differences in the two functions. Our query will again focus upon Warehouse Cost, and, except for the use of the new function, be identical in every respect to our last example.

We can revisit the results obtained in our first query, and displayed in Illustration 1, to see the total organization Warehouse Cost returned for each month. Just as we did in our practice exercise for the OpeningPeriod () function, we can use these values as an easy means of verifying our results with the ClosingPeriod() function below.

Let's say, for the purposes of this exercise, that we are informed of a business requirement to present closing balances on the Warehouse Cost accounts for each quarter of 1997 and 1998. We can see, from the month values shown in Illustration 1, what these totals should be. We will use the ClosingPeriod () function to meet the stated requirement, with the month values available, as before, to help us verify our results.

-- MDX10-3:  Tutorial Query No. 3
WITH 
   MEMBER [Measures].[Closing Bal] AS
  '([Measures].[Warehouse Cost], 
    ClosingPeriod ( [Time].[Month], [Time].CurrentMember))'
SELECT    
   {[Measures].[Closing Bal]} ON COLUMNS,    
     [Time].[Quarter].Members ON ROWS
FROM Warehouse

As soon Analysis Services fills the specified cells in the Results pane, we see the result dataset shown in Illustration 3.

The query delivers the results we anticipate from our discussion of the syntax of the ClosingPeriod() function: The value returned for each quarter coincides with the value of the last month, which we have defined as the Closing Period ("Closing Bal") in our calculated member, via the WITH clause in the query above. We see that the value of the last member of the month level, belonging to each quarter (which we again have chosen to display in the example) is returned.

We can verify correctness by looking again at the results we obtained in our first, exploratory query, depicted in Illustration 1. For example, the Closing Period for Q1 in each of the two years selected is 6285.76 and 12,128.04, corresponding to each respective month 12 in Illustration 1.

The ClosingPeriod () function thus lives up to its design to provide a means of fulfilling a common need of business users. Coupling the function with Time.CurrentMember in the argument, as shown in our query, the logic is applicable from any point in the Time dimension we happen to occupy, just as it was in the OpeningPeriod() function with which we initially practiced .

In conclusion, we can easily see the utility of the OpeningPeriod() and ClosingPeriod () functions in providing us a relative, direct route to selecting the first or last member, respectively, of a level that we target in the argument of the function.

Next in Our Series ...

In this lesson, we delved further into the time series functions group, examining two functions designed especially to focus on opening and closing periods for a given balance. After discussing the common business need to analyze data over time, and, more specifically, within a scope of the opening or closing periods of a given time window, we overviewed OpeningPeriod() and ClosingPeriod () functions in detail. In the case of each, we illustrated the syntax that is appropriate for its effective use, and then tested our understanding of how to leverage the function by undertaking a relevant practice exercise.

As has been the case with numerous functions in past lessons, we revisited the construction of a calculated member, building a calculated measure within our practice queries as a vehicle for illustrating the operation of the OpeningPeriod() and ClosingPeriod () functions. Finally, we discussed the results that each practice example generated, comparing the results of the ClosingPeriod () function with the OpeningPeriod() function to emphasize their similarity in construction and operation.

In our next lesson, we will conclude our three-article focus on the time series functions group with an examination of the LastPeriods() and ParallelPeriods() functions.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.