Tutorial Gateway

MDX OpeningPeriod Function

by

The MDX OpeningPeriod Function will return the First Sibling member belongs to the specified member or at specified level.

MDX OpeningPeriod Function Syntax

The basic syntax of the OpeningPeriod in Multidimensional Expression is as shown below:

OPENINGPERIOD (Level_Expression, Member_Expression)
  • Member_Expression: Any Multidimensional Expression that returns valid Member.
  • Level_Expression: Please specify the level you want to navigate.

In this article we will show you, How to write OpeningPeriod function in MDX query with examples. For this, We are going to use the below show data

Following screenshot shows the List of Calender Years inside the Date Dimension

MDX OPENINGPERIOD FUNCTION

MDX OpeningPeriod Function Example

In this example we are going to find the First year present in the Calender Year using OpeningPeriod function.

MDX CODE

SELECT 
  [Measures].[Internet Sales Amount] ON COLUMNS,
  OPENINGPERIOD ([Date].[Calendar].[Calendar Year]) ON ROWS
FROM [Adventure Works]

OUTPUT

MDX OPENINGPERIOD FUNCTION 1

ANALYSIS:

In the above MDX Query, We used [Internet Sales amount] on the columns

[Measures].[Internet Sales Amount] ON COLUMNS

Below line of code will return the first sibling member present in the Calender Year from the Date Dimension.

OPENINGPERIOD ([Date].[Calendar].[Calendar Year])

MDX OpeningPeriod Function Example 2

In this example we are going to find the First Month present in the Calender Year 2013 and calculate the Internet Sales Amount of that using OpeningPeriod function.

MDX CODE

SELECT 
  [Measures].[Internet Sales Amount] ON COLUMNS,
  OPENINGPERIOD (
             [Date].[Calendar].[Month] 
            ,[Date].[Calendar].[Calendar Year].[CY 2013]
		) ON ROWS
FROM [Adventure Works]

OUTPUT

MDX OPENINGPERIODS FUNCTION 2

Finding Range Using MDX OpeningPeriod Example 1

In this example we will show you, How to find the Range using OpeningPeriod function. The following query will return the Internet Sales amount from Calender Year 2005 to 2013

MDX CODE

SELECT 
  [Measures].[Internet Sales Amount] ON COLUMNS,
  OPENINGPERIOD ([Date].[Calendar].[Calendar Year]):
  [Date].[Calendar].[Calendar Year].[CY 2013] ON ROWS
FROM [Adventure Works]

OUTPUT
MDX OPENINGPERIODS FUNCTION 3

ANALYSIS:

In the above MDX Query, We used [Internet Sales amount] on the columns

[Measures].[Internet Sales Amount] ON COLUMNS

Below line of code will return the first sibling member present in the Calender Year from the Date Dimension, Which is 2005.

OPENINGPERIOD ([Date].[Calendar].[Calendar Year])

It means, We used the range operator between 2005 and 2013 Year so, the output will display the Internet Sales amount of all the Calender Years present in the Date dimension.

OPENINGPERIOD ([Date].[Calendar].[Calendar Year]): -- 2005

[Date].[Calendar].[Calendar Year].[CY 2013] -- 2013

Finding Range Using MDX OpeningPeriod Example 2

In this example we will show you, How to find the Range using OpeningPeriod function. The following query will return the Internet Sales amount from January to July Month in Calender Year 2013

MDX CODE

SELECT 
  [Measures].[Internet Sales Amount] ON COLUMNS,
  OPENINGPERIOD (
             [Date].[Calendar].[Month] --January 2013 
            ,[Date].[Calendar].[Calendar Year].[CY 2013]
		): 
  [Date].[Calendar].[Month].[July 2013] ON ROWS
FROM [Adventure Works]

OUTPUT

MDX OPENINGPERIODS FUNCTION 4

Thank you for Visiting Our Blog

Placed Under: MDX