Export (0) Print
Expand All
Avg
IIf
Lag
Max
Min
Mtd
Qtd
Sum
Var
Wtd
Ytd
Expand Minimize

ClosingPeriod (MDX)

Updated: 17 July 2006

Returns the member that is the last sibling among the descendants of a specified member at a specified level.


ClosingPeriod( [ Level_Expression [ ,Member_Expression ] ] )

Level_Expression

A valid Multidimensional Expressions (MDX) expression that returns a level.

Member_Expression

A valid Multidimensional Expressions (MDX) expression that returns a member.

This function is primarily intended to be used against a dimension with a type of Time, but can be used with any dimension.

  • If a level expression is specified, the ClosingPeriod function uses the dimension that contains the specified level and returns the last sibling among the descendants of the default member at the specified level.
  • If both a level expression and a member expression are specified, the ClosingPeriod function returns the last sibling among the descendants of specified member at the specified level.
  • If neither a level expression nor a member expression is specified, the ClosingPeriod function uses the default level and member of the dimension (if any) in the cube with a type of Time.

The ClosingPeriod function is equivalent to the following MDX statement:

BottomCount(Descendants(Member_Expression, Level_Expression), 1).

ms145584.note(en-US,SQL.90).gifNote:
The OpeningPeriod function is similar to the ClosingPeriod function, except that the OpeningPeriod function returns the first sibling instead of the last sibling.

Examples

The following example returns the value for the default measure for FY2005 member of the Date dimension (which has a semantic type of Time). This member is returned because the Fiscal Year level is the first descendant of the [All] level, the Fiscal hierarchy is the default hierarchy because it is the first user-defined hierarchy in the hierarchy collection, and the FY 2005 member is the last sibling in this hierarchy at this level.

SELECT ClosingPeriod() ON 0
FROM [Adventure Works]

The following example returns the value for the default measure for August 31, 2004 member at the Date.Date.Date level for the Date.Date attribute hierarchy. This member is the last sibling of the descendant of [All] level in the Date.Date attribute hierarchy.

SELECT ClosingPeriod ([Date].[Date].[Date]) ON 0
FROM [Adventure Works]

The following example returns the value for the default measure for December, 2003 member, which is the last sibling of the descendant of the 2003 member at the year level in the Calendar user-defined hierarchy.

SELECT ClosingPeriod ([Date].[Calendar].[Month],[Date].[Calendar].[Calendar Year].&[2003]) ON 0
FROM [Adventure Works]

The following example returns the value for the default measure for June, 2003 member, which is the last sibling of the descendant of the 2003 member at the year level in the Fiscal user-defined hierarchy.

SELECT ClosingPeriod ([Date].[Fiscal].[Month],[Date].[Fiscal].[Fiscal Year].&[2003]) ON 0
FROM [Adventure Works]

Release History

17 July 2006

Changed content:
  • Updated syntax and arguments to improve clarity.
  • Added updated examples.

Community Additions

ADD
Show:
© 2014 Microsoft