OpeningPeriod(), ClosingPeriod() – MDX

Hi,

Today let’s learn two important functions in SSAS

  1. OpeningPeriod()
  2. ClosingPeriod()

In a BI reporting we definitely need to know the opening and closing time dimension at any level of the hierarchy

lets look at the behavior of these two functions and then we will go through some examples

ClosingPeriod()

OpeningPeriod (MDX)

Returns the first sibling among the descendants of a specified level, optionally at a specified member.

OpeningPeriod( [ 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 the Time dimension, but can be used with any dimension.

  • If a level expression is specified, the OpeningPeriod function uses the hierarchy that contains the specified level and returns the first sibling among the descendants of the default member at the specified level.
  • If both a level expression and a member expression are specified, the OpeningPeriod function returns the first sibling among the descendants of specified member at the specified level within the hierarchy containing the specified level.
  • If neither a level expression nor a member expression are specified, the OpeningPeriod function uses the default level and member of the dimension with a type of Time.

Examples

The following example returns the value for the default measure for the FY2002 member of the Date dimension (which has a 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 FY2002 member is the first sibling in this hierarchy at this level.

SELECT OpeningPeriod() ON 0
FROM [Adventure Works]

The following example returns the value for the default measure for July 1, 2001 member at the Date.Date.Date level for the Date.Date attribute hierarchy. This member is the first sibling of the descendant of [All] level in the Date.Date attribute hierarchy.

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

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

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

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

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

ClosingPeriod (MDX)


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:

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


Examples

The following example returns the value for the default measure for FY2007 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 2007 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 November 30, 2006 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]
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s