MDX Time Series Functions, Part II: The OpeningPeriod () and ClosingPeriod() Functions - Page 3August 11, 2003 The ClosingPeriod() FunctionThe 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. DiscussionThe 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. SyntaxSyntactically, 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] ) PracticeLet'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. 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 |