dcsimg

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.

8.      Type the following query into the Query pane:

-- 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

9.      Execute the query by clicking the Run button on the toolbar atop the Sample Application.

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


Illustration 3: The Query Result Dataset

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 .

10.      Save the query as desired by selecting File -> Save As, and giving the file a meaningful name and location.

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.

MDX Essentials Series
The LEVEL_NUMBER Member Property
The LEVEL_UNIQUE_NAME Intrinsic Member Property
Intrinsic Member Properties: The HIERARCHY_UNIQUE_NAME Property
Intrinsic Member Properties: The DIMENSION_UNIQUE_NAME Property
Further Combination of BottomCount() with Other MDX Functions
Combine BottomCount() with Other MDX Functions to Add Sophistication
Basic Set Functions: The BottomCount() Function, Part I
Intrinsic Member Properties: The MEMBER_VALUE Property
Intrinsic Member Properties: The MEMBER_UNIQUE_NAME Property
Intrinsic Member Properties: The MEMBER_NAME Property
Intrinsic Member Properties: The MEMBER_KEY Property
Intrinsic Member Properties: The MEMBER_CAPTION Property
Set Functions: The StripCalculatedMembers() Function
Set Functions: The AddCalculatedMembers() Function
MDX Numeric Functions: The Min() Function
MDX Numeric Functions: The Max() Function
Set Functions: The .AllMembers Function
MDX Essentials: Set Functions: The MeasureGroupMeasures() Function
String Functions: The .Properties Function, Part II
String Functions: The .Properties Function
Logical Functions: IsGeneration(): Conditional Logic within Filter Expressions
MDX Scripting Statements: Introducing the Simple CASE Statement
Logical Functions: IsGeneration(): Conditional Logic within Calculations
Logical Functions: IsAncestor(): Conditional Logic within Filter Expressions
MDX Clauses and Keywords: Use HAVING to Filter an Axis
Logical Functions: IsAncestor(): Conditional Logic within Calculations
Logical Functions: IsSibling(): Conditional Logic within Filter Expressions
Logical Functions: IsSibling(): Conditional Logic within Calculations
MDX Operators: The IsLeaf() Operator: Conditional Logic within Filter Expressions
MDX Operators: The IsLeaf() Operator: Conditional Logic within Calculations
MDX Numeric Functions: The .Ordinal Function
Other MDX Entities: Perspectives
MDX Operators: The IS Operator
MDX Set Functions: The Distinct() Function
MDX Set Functions: The ToggleDrillState() Function
Set Functions: The DrillUpLevel() Function
Set Functions: The DrillDownLevelTop() and DrillDownLevelBottom() Functions
MDX Set Functions: DrillDownLevel()
MDX Set Functions: The DRILLUPMEMBER() Function
MDX Essentials: Set Functions: The DRILLDOWNMEMBERTOP() and DRILLDOWNMEMBERBOTTOM() Functions
MDX Essentials : Set Functions: The DRILLDOWNMEMBER() Function
MDX Essentials: Drilling Through with MDX: The DRILLTHROUGH Statement
MDX Essentials: String Functions: The .UniqueName Function
MDX Essentials: String Functions: The .Name Function
MDX Essentials: String / Numeric Functions: The CoalesceEmpty() Function
MDX Essentials: Basic Set Functions: The TopCount() Function, Part II
MDX Essentials: Basic Set Functions: The TopCount() Function, Part I
MDX Essentials: Enhancing CROSSJOIN() with Calculated Members
MDX Essentials: Set and String Functions: The GENERATE() Function
MDX Essentials: The CROSSJOIN() Function: Breaking Bottlenecks
MDX Essentials: String / Numeric Functions: More on the IIF() Function
MDX Essentials: String / Numeric Functions: Introducing the IIF() Function
MDX Essentials: Logical Functions: The IsEmpty() Function
MDX Essentials: Basic Set Functions: The EXTRACT() Function
MDX Essentials: Numeric Functions: Introduction to the AVG() Function
MDX Essentials: Basic Member Functions: The .Item() Function
MDX Essentials: Basic Set Functions: Subset Functions: The Subset() Function
MDX Essentials: Basic Set Functions: Subset Functions: The Tail() Function
MDX Essentials: Basic Set Functions: Subset Functions: The Head() Function
MDX Essentials: Basic Set Functions: The CrossJoin() Function
MDX Essentials: Basic Numeric Functions: The Count() Function
MDX Essentials: Basic Set Functions: The Filter() Function
MDX Essentials: Basic Set Functions: The EXCEPT() Function
MDX Essentials: Basic Set Functions: The Intersect() Function
MDX Essentials: Basic Set Functions: The Union() Function
MDX Essentials: Basic Set Functions: The Order() Function
MDX Essentials - MDX Time Series Functions, Part III: The LastPeriods() and ParallelPeriod() Functions
MDX Time Series Functions, Part II: The OpeningPeriod () and ClosingPeriod() Functions
MDX Essentials - MDX Time Series Functions, Part I: PeriodsToDate() and Kindred Functions
MDX Essentials: MDX Member Functions: "Relative" Member Functions
MDX Member Functions: The Cousin () Function
MDX Essentials: Member Functions: More "Family" Functions
MDX Member Functions: The "Family" Functions
MDX Essentials: MDX Members: Introducing Members and Member
MDX Essentials : MDX Operators: The Basics
MDX Essentials: Structure of the MDX Data Model
MDX at First Glance: Introduction to SQL Server MDX Essentials








The Network for Technology Professionals

Search:

About Internet.com

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