In Multidimensional Expression, 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 MDX OpeningPeriod is:
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 Syntax
The basic syntax of the MDX OpeningPeriod is:
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 Example
In this example we are going to find the First year present in the Calender Year using OpeningPeriod function.
CODE
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS,
OPENINGPERIOD ([Date].[Calendar].[Calendar Year]) ON ROWS
FROM [Adventure Works]
OUTPUT
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.
CODE
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS,
OPENINGPERIOD (
[Date].[Calendar].[Month]
,[Date].[Calendar].[Calendar Year].[CY 2013]
) ON ROWS
FROM [Adventure Works]
OUTPUT
In this example we are going to find the First year present in the Calender Year using OpeningPeriod function.
CODE
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS,
OPENINGPERIOD ([Date].[Calendar].[Calendar Year]) ON ROWS
FROM [Adventure Works]
OUTPUT
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.
CODE
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS,
OPENINGPERIOD (
[Date].[Calendar].[Month]
,[Date].[Calendar].[Calendar Year].[CY 2013]
) ON ROWS
FROM [Adventure Works]
OUTPUT
Finding Range Using MDX OpeningPeriod Function 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
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
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 Function 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
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
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
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
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 Function 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
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
