MDX OPENINGPERIODS FUNCTION 2

MDX OpeningPeriod Function

The MDX OpeningPeriod Function will return the First Sibling member belongs to the specified member or at a 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.

For this OpeningPeriod function in MDX query, We are going to use the below show data. The following screenshot shows the List of Calendar 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 Calendar Year using the OpeningPeriod function.

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

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

[Measures].[Internet Sales Amount] ON COLUMNS

The below line of code will return the first sibling member present in the Calendar 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 Calendar Year 2013 and calculate the Internet Sales Amount of that using OpeningPeriod function.

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

Finding Range Using MDX OpeningPeriod Example 1

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

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

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

[Measures].[Internet Sales Amount] ON COLUMNS

The below line of code will return the first sibling member present in the Calendar 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 Calendar 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 show you how to find the Range using the MDX OpeningPeriod function. The following query will return the Internet Sales amount from January to July Month in Calendar Year 2013

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]
MDX OPENINGPERIODS FUNCTION 4