MDX OpeningPeriod Function

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 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

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

MDX NextMember Function

In Multidimensional Expression, NextMember function will return the same level member at the Next position.
MDX Nextmember Function Syntax

The basic syntax of the MDX Nextmember is:

Member_Expression.NEXTMEMBER

Member_Expression: Any Multidimensional Expression that returns valid Member.


NOTE: Nextmember Function is used to move forward between Members at same level. For example, If we mentioned City member as Member_Expression then Nextmember function will move forward between remaining Cities, it will not go Level Up (State – Province) or Level down (Postal Code).


In this article we will show you, How to write Nextmember function with examples. For this, we are going to use below shown data.

MDX Nextmember Function Example

In this example we will show you, How to write the NextMember function in Multidimensional Expression query. The following query will return the Reseller Sales amount of the Calender Year 2012 because [CY 2011].NEXTMEMBER will look for year after 2011, which is 2012.


CODE


SELECT [Date].[Calendar].[Calendar Year].[CY 2011].NextMember ON COLUMNS,
[Measures].[Reseller Sales Amount] ON ROWS
FROM [Adventure Works] 

OUTPUT

MDX Nextmember Function Example 2

In this example we will show you, How to write the multiple NextMember function in single Multidimensional Expression query. The following query will return the Reseller Sales amount of the Calender Year 2013.


CODE

SELECT [Date].[Calendar].[Calendar Year].[CY 2011].NEXTMEMBER.NEXTMEMBER ON 0
,[Measures].[Reseller Sales Amount] ON ROWS
FROM [Adventure Works] 

OUTPUT





Analysis:

First, [CY 2011].NEXTMEMBER will look for year after 2011, which is 2012


[Date].[Calendar].[Calendar Year].[CY 2011].NextMember

Second, [CY 2011].Nextmember.Nextmember will look for year after 2012, which is 2013

[Date].[Calendar].[Calendar Year].[CY 2011].NextMember.NextMember


Finding Range Using MDX Nextmember Function


In this example we will show you, How to find the Range using Nextmember function. The following query will return the Reseller Sales amount from Calender Year 2011 to 2013 because we used the range symbol (:) between


[CY 2011] and [CY 2011].Nextmember.Nextmember.


CODE


SELECT
[Date].[Calendar].[Calendar Year].[CY 2011]:
[Date].[Calendar].[Calendar Year].[CY 2011].NextMember.NextMember ON 0
,[Measures].[Reseller Sales Amount] ON ROWS
FROM [Adventure Works]

OUTPUT



MDX Nextmember Function Alternative 1


In this example, We are going to use Lead Function with Positive integer value to achieve above results. Please refer MDX Lead Function article to understand the function


CODE


SELECT
[Date].[Calendar].[Calendar Year].[CY 2011]:
[Date].[Calendar].[Calendar Year].[CY 2011].LEAD(2) ON 0
,[Measures].[Reseller Sales Amount] ON ROWS
FROM [Adventure Works]


OUTPUT

MDX Nextmember Function Alternative 2

In this example we will show you, We are going to use Lag Function with Negative integer value. Please refer MDX Lag Function article to understand the function


CODE


SELECT
[Date].[Calendar].[Calendar Year].[CY 2011]:
[Date].[Calendar].[Calendar Year].[CY 2011].LAG(-2) ON 0
,[Measures].[Reseller Sales Amount] ON ROWS
FROM [Adventure Works] 

OUTPUT

MDX Lead Function

In Multidimensional Expression, Lead function is used to navigate between same level members and returns the members at the specified position. For example, If you want to navigate to particular member then we can use this LEAD function.


TIP: In MDX, Lag Function with Negative value is Equal to Lead function with Positive Value.
MDX Lead Function Syntax


The basic syntax of the MDX Lead is:

Member_Expression.LEAD (Member_Position)

Member_Expression: Any Multidimensional Expression that returns valid Member.


Member_Position: Please specify the position of a member you want to Navigate.
If we use Zero as the Member_Position then Lead Function will write the same Member_Expression that we mentioned before Lead function.
If we use Positive Value as the Member_Position then Lead Function will move forward to specified value and returns the Member_Expression at that position.
If we use Negative Value as the Member_Position then Lead Function will move Backwards to specified value and returns the Member_Expression at that position.


NOTE: Lead Function is used to navigate between same Members level. For example, If we mentioned City member as Member_Expression then Lead function will navigate between remaining Cities, it will not go Level Up (State – Province) or Level down (Postal Code).


In this article we will show you, How to write Lead function to navigate both forward and backward with examples. For this, we are going to use below shown data.

MDX Lead Function with Zero

In this example we will show you, What happen when we use Zero value for the Lead Function. The following query will return the Reseller Sales amount and Tax amount of the Calender Year 2013 itself.


CODE


SELECT {
[Measures].[Reseller Sales Amount]
,[Measures].[Reseller Tax Amount] } ON COLUMNS,
[Date].[Calendar].[Calendar Year].[CY 2013].LEAD(0) ON ROWS
FROM [Adventure Works]


OUTPUT

MDX Lead Function with Positive Value

In this example we will show you, What happen when we use Positive integer value in Lead Function. The following query will return the Reseller Sales amount and Tax amount of the Calender Year 2013 because Lead function with positive value (3) will move forward 3 years from the given year (2010 + 3 = 2013)


CODE


SELECT {
[Measures].[Reseller Sales Amount]
,[Measures].[Reseller Tax Amount] } ON COLUMNS,
[Date].[Calendar].[Calendar Year].[CY 2010].LEAD(3) ON ROWS
FROM [Adventure Works]


OUTPUT

MDX Lead Function with Negative Value

In this example we will show you, What happen when we use Negative integer value for the Lead Function. The following query will return the Reseller Sales amount and Tax amount of the Calender Year 2010 because Lead function with negative value (-3) will move back 3 years from the given year (2013 – 3 = 2010)


CODE


SELECT {
[Measures].[Reseller Sales Amount]
,[Measures].[Reseller Tax Amount] } ON COLUMNS,
[Date].[Calendar].[Calendar Year].[CY 2013].LEAD(-3) ON ROWS
FROM [Adventure Works]


OUTPUT

Finding Range Using MDX Lead Function

In this example we will show you, How to find the Range using Lead function. The following query will return the Reseller Sales amount and Tax amount from Calender Year 2010 to 2013 because we used the range symbol (:) between [CY 2010] and [CY 2010].LEAD (3).


CODE


SELECT {
[Measures].[Reseller Sales Amount]
,[Measures].[Reseller Tax Amount] } ON COLUMNS,
[Date].[Calendar].[Calendar Year].[CY 2010]:
[Date].[Calendar].[Calendar Year].[CY 2010].LEAD(3) ON ROWS
FROM [Adventure Works]

OUTPUT





If you want to find the range by moving backwards then use following MDX query


CODE


SELECT {
[Measures].[Reseller Sales Amount]
,[Measures].[Reseller Tax Amount] } ON COLUMNS,
[Date].[Calendar].[Calendar Year].[CY 2013]:
[Date].[Calendar].[Calendar Year].[CY 2013].LEAD(-3) ON ROWS
FROM [Adventure Works]

MDX Lag Function

In Multidimensional Expression, Lag function is used to navigate between same level members and returns the members at the specified position. For example, If you want to navigate to particular member then we can use this Lag function.


TIP: In MDX, Lead Function with Positive Value is Equal to Lag with Negative value.
MDX Lag Function Syntax


The basic syntax of the MDX Lag is:

Member_Expression.LAG (Member_Position)

Member_Expression: Any Multidimensional Expression that returns valid Member.


Member_Position: Please specify the position of a member you want to Navigate.
If we use Zero as the Member_Position then Lag Function will write the same Member_Expression that we mentioned before Lag function.
If we use Negative Value as the Member_Position then Lag Function will move forward to specified value and returns the Member_Expression at that position.
If we use Positive Value as the Member_Position then Lag Function will move Backwards to specified value and returns the Member_Expression at that position.


NOTE: Lag Function is used to navigate between same Members level. For example, If we mentioned City member as Member_Expression then Lag function will navigate between remaining Cities, it will not go Level Up (State – Province) or Level down (Postal Code).


In this article we will show you, How to write Lag function to navigate both forward and backward with examples. For this, we are going to use below shown data.

MDX Lag Function with Zero

In this example we will show you, What happen when we use Zero value for the Lag Function. The following query will return the Reseller Sales amount and Tax amount of the Calender Year 2013 itself.


CODE


SELECT {
[Measures].[Reseller Sales Amount]
,[Measures].[Reseller Tax Amount] } ON COLUMNS,
[Date].[Calendar].[Calendar Year].[CY 2013].LAG(0) ON ROWS
FROM [Adventure Works]


OUTPUT

MDX Lag Function with Positive Value

In this example we will show you, What happen when we use Positive integer value in Lag Function. The following query will return the Reseller Sales amount and Tax amount of the Calender Year 2010 because Lag function with Positive value (3) will move back 3 years from the given year (2013 – 3 = 2010)


CODE

SELECT {
[Measures].[Reseller Sales Amount]
,[Measures].[Reseller Tax Amount] } ON COLUMNS,
[Date].[Calendar].[Calendar Year].[CY 2013].LAG(3) ON ROWS
FROM [Adventure Works]


OUTPUT

MDX Lag Function with Negative Value

In this example we will show you, What happen when we use Negative integer value for the Lag Function. The following query will return the Reseller Sales amount and Tax amount of the Calender Year 2013 because Lag function with Negative value (-2) will move forward 2 years from the given year (2011 + 2 = 2013)


CODE


SELECT {
[Measures].[Reseller Sales Amount]
,[Measures].[Reseller Tax Amount] } ON COLUMNS,
[Date].[Calendar].[Calendar Year].[CY 2011].LAG(-2) ON ROWS
FROM [Adventure Works]


OUTPUT

Finding Range Using MDX Lag Function

In this example we will show you, How to find the Range using Lag function. The following query will return the Reseller Sales amount and Tax amount from Calender Year 2010 to 2013 because we used the range symbol (:) between [CY 2013] and [CY 2013].LAG (3).


CODE


SELECT {
[Measures].[Reseller Sales Amount]
,[Measures].[Reseller Tax Amount] } ON COLUMNS,
[Date].[Calendar].[Calendar Year].[CY 2013]:
[Date].[Calendar].[Calendar Year].[CY 2013].LAG(3) ON ROWS
FROM [Adventure Works]


OUTPUT





If you want to find the range by moving forward then use Lag function with negative value.

MDX LastPeriods Function

In Multidimensional Expression, LastPeriods function is used to navigate between same level members and returns the set of members up to the specified position including the given number itself. For example, If you want to list the members from one position to particular member then we can use this LastPeriods function.

TIP: If you know the index position of your destination member you can use this LastPeriod function.
MDX LastPeriods Function Syntax

The basic syntax of the MDX LastPeriods is:


LASTPERIODS (Member_Position, Member_Expression)

Member_Expression: Any Multidimensional Expression that returns valid Member.


Member_Position: Please specify the position of a member you want to Navigate.
If we use Zero as the Member_Position then LastPeriods Function will return the empty set.
If we use Negative Value as the Member_Position then LastPeriods Function will move forward to specified value and returns the set of members up to that position including the Member_Expression.
If we use Positive Value as the Member_Position then LastPeriods Function will move Backwards to specified value and returns the set of members up to that position including the Member_Expression.


NOTE: LastPeriods Function is used to navigate between same Members level. For example, If we mentioned City member as Member_Expression then LastPeriods function will navigate between remaining Cities, it will not go Level Up (State – Province) or Level down (Postal Code).


In this article we will show you, How to write LastPeriods function to navigate both forward and backward with examples. For this, we are going to use below shown data.

MDX LastPeriods Function with Zero

In this example we will show you, What happen when we use Zero value for the LastPeriods Function. The following query will return the Empty set


CODE


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


OUTPUT

MDX LastPeriods Function with Positive Value

In this example we will show you, What happen when we use Positive integer value in LastPeriods Function. The following query will return the Internet Sales amount of the Calender Year 2013, 2012 and 2011 because LastPeriods function with Positive value (3) will move back 2 years + year 2013. This is because LastPeriods function includes 2013 itself.


CODE


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


OUTPUT

MDX LastPeriods Function with Negative Value

In this example we will show you, What happen when we use Negative integer value for the LastPeriods Function. The following query will return the Internet Sales amount of the Calender Year 2010, 2011, 2012 and 2013 because LastPeriods function with Negative value (-4) will move forward 3 years + year 2010. This is because LastPeriods function includes 2010 itself.


CODE


SELECT
[Measures].[Internet Sales Amount] ON COLUMNS,
LASTPERIODS (
-4,
[Date].[Calendar].[Calendar Year].[CY 2010]
) ON ROWS
FROM [Adventure Works]


OUTPUT

MDX LastSibling Function

In Multidimensional Expression, LastSibling function will return the Last Child member that belongs to the parent of a specified member. For example, If you know single customer name and you want to find the Sales of a Last customer then you can use this LastSibling function.


TIP: You can use MDX FirstSibling Function to find the First Sibling.
MDX LastSibling Function Syntax


The basic syntax of the MDX LastSibling is:

Member_Expression.LASTSIBLING

Member_Expression: Any Multidimensional Expression that returns valid Member.


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


Following screenshot shows the Countries inside the Geography





Following screenshot shows the [State – Provinces] inside the France Country






MDX LastSibling Function Example

If we know the Loiret is one of the state in France and our intention is to find the Last state present in France then, we can use this LastSibling function. In this example we are going to find the Last Children present in the State Province list and calculate the Reseller Sales Amount of that.


CODE


SELECT
[Measures].[Reseller Sales Amount] ON COLUMNS,
[Geography].[Geography].[State-Province].[Loiret].LASTSIBLING ON ROWS
FROM [Adventure Works];


OUTPUT



MDX LastSibling Function Example


In this example we are going to find the Last Children present in the Countries list.


CODE


SELECT
[Measures].[Reseller Sales Amount] ON COLUMNS,
[Geography].[Geography].[Country].[France].LASTSIBLING ON ROWS
FROM [Adventure Works];


OUTPUT





Analysis:

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

[Measures].[Reseller Sales Amount] ON COLUMNS


Below line of code will check for the France parent (Which is All member) and then finds the Last child member of the Country.


[Geography].[Geography].[Country].[France].LASTSIBLING


MDX LastSibling Function Alternative


In this example we are going to use the LastSibling function alternative to achieve the same result. Please refer MDX Parent Function to understand the Parent function and refer MDX LastChild function to understand the LastChild function.


CODE


SELECT
[Measures].[Reseller Sales Amount] ON COLUMNS,
[Geography].[Geography].[Country].[France].PARENT.LASTCHILD ON ROWS
FROM [Adventure Works];


OUTPUT

MDX LastChild Function

In Multidimensional Expression, LastChild function will return the Last Child member belongs to the specified member. For example, If you know single customer name and if you want to find the Sales of a Last customer then you can use this LastChild function.

TIP: We can use MDX FirstChild Function to find the First Child Member.
MDX LastChild Function Syntax

The basic syntax of the MDX LastChild is:

Member_Expression.LASTCHILD

Member_Expression: Any Multidimensional Expression that returns valid Member.


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


Following screenshot shows the Countries inside the Geography





Following screenshot shows the [State – Provinces] inside the France Country






MDX LastChild Function Example


In this example we are going to find the Last Children present in the France Country. It means Last State present in the France country.


CODE


SELECT
[Measures].[Reseller Sales Amount] ON COLUMNS,
[Geography].[Geography].[Country].[France].LASTCHILD ON ROWS
FROM [Adventure Works];


OUTPUT





Analysis:

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

[Measures].[Reseller Sales Amount] ON COLUMNS


Below line of code will write the Last child member of the France from all the State-Provices present in the France country.

[Geography].[Geography].[Country].[France].LASTCHILD

For Yveline State, there is no sales at all so, it is displaying Null results.
MDX LastChild Function Example 2

In this example we are going to find the Last Children present in the Countries list and calculate the Reseller Sales Amount of that.


CODE


SELECT
[Measures].[Reseller Sales Amount] ON COLUMNS,
[Geography].[Geography].[Country].[France].PARENT.LASTCHILD ON ROWS
FROM [Adventure Works];


OUTPUT





Analysis:

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

[Measures].[Reseller Sales Amount] ON COLUMNS

From Below line of code,

[Geography].[Geography].[Country].[France].PARENT.LASTCHILD


MDX will first implement the Parent function to find the parent member of a France, Which is All Members. Please refer MDX Parent Function for further understanding

Next, It will implement LastChild function to find the Last child member of the Countries List Which is United States.
MDX LastChild Function Alternative

In this example we are going to use the LastChild function alternative to achieve the same result. Please refer MDX LastSibling Function to understand the LastSibling function.


CODE


SELECT
[Measures].[Reseller Sales Amount] ON COLUMNS,
[Geography].[Geography].[Country].[France].LASTSIBLING ON ROWS
FROM [Adventure Works];


OUTPUT

MDX Head Function

In Multidimensional Expression, Head Function is used to select the required number of records from the source data.

For example, If you want to find the Top 10 performing products or you want to find the top 10 regions with highest sales or to calculate Highest Salary we can use this Head function along with MDX Order Function.
Similarities between MDX HEAD and SQL TOP Clause


MDX HEAD Function is similar to SQL TOP Clause.


Like Top clause, Head function extract the required number of records in the order they initially stored.
To extract Top 10 performing records we have to use Order BY Clause in SQL.
To extract Top 10 performing records we have to use the MDX Order Function with appropriate Measure or Numeric Value.
MDX Head Function Syntax


MDX Head Function allows only two arguments. The basic syntax of this function is:

HEAD (Set_Expression, Count)


Set_Expression: Any Multidimensional Expression or attributes on which you want check.
Count: Please provide, How many number of records you want to retrieve.


In this article we will show you, How to write HEAD Function to extract Top 7 States from Geography dimension (present in the Adventure Works Cube) whose Reseller Sales amount is greater than others with example. For this, we are going to use below shown data.

MDX Head Function Example

In this example we will show you, How to write the Head function. The following query will return first seven records from the above source in the order they appear above.


CODE

SELECT {[Measures].[Reseller Sales Amount]} ON COLUMNS,
HEAD (
[Geography].[Geography].[State-Province]
,7
) ON ROWS
FROM [Adventure Works]

In the above MDX Query, We selected the [Reseller Sales Amount] Measure on the Columns and [State Province] attribute present in the geography Dimension on Rows. Next, Head function will select the Top 7 records irrespective of their measure values.


OUTPUT





No one will accept the above result as top 7 performing states around the world. They we just first 7 records.
Using Order Function along with MDX Head Function


In this example we will show you, What happen when we add the MDX Order Function.


CODE


SELECT {[Measures].[Reseller Sales Amount]} ON COLUMNS,
ORDER (
HEAD ([Geography].[Geography].[State-Province], 7)--Already Picked Data
,[Measures].[Reseller Sales Amount], DESC
) ON ROWS
FROM [Adventure Works]


OUTPUT





Analysis:

First Head function will select the First 7 records present in the source data using below statement

HEAD ([Geography].[Geography].[State-Province], 7)

Next, Order function outside the Head function will sort the already picked data in the descending order using their Reseller Sales Amount.


ORDER (
HEAD ([Geography].[Geography].[State-Province], 7)
,[Measures].[Reseller Sales Amount], DESC
) ON ROWS


It means, We are getting First 7 records from the source data in Descending order

Let us change the above code:


CODE


SELECT {[Measures].[Reseller Sales Amount]} ON COLUMNS,
HEAD (
ORDER (
[Geography].[Geography].[State-Province]
,[Measures].[Reseller Sales Amount]
,BDESC
)
,7
) ON ROWS
FROM [Adventure Works]


OUTPUT





Analysis

First, Order function will sort the State Province data in the Descending order using their Reseller Sales Amount. We used BDESC to break the hierarchy while sorting the data.


ORDER (
[Geography].[Geography].[State-Province]
,[Measures].[Reseller Sales Amount]
,BDESC
)

Next, Head function will pick the First 7 records from the already sorted data.

HEAD (
ORDER (
[Geography].[Geography].[State-Province]
,[Measures].[Reseller Sales Amount]
,BDESC
)
,7
) ON ROWS


Now, We got our Top 7 performing states.

NOTE: Please use the appropriate Measure as second argument in the ORDER function otherwise, You will end up with wrong results.

MDX FirstSibling Function

In Multidimensional Expression, FirstSibling function will return the First Child member that belongs to the parent of a specified member. For example, If you know single customer name and you want to find the Sales of a first customer then you can use this FirstSibling function.
MDX FirstSibling Function Syntax

The basic syntax of the MDX FirstSibling is:

Member_Expression.FIRSTSIBLING

Member_Expression: Any Multidimensional Expression that returns valid Member.


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


Following screenshot shows the Countries inside the Geography





Following screenshot shows the [State – Provinces] inside the France Country

MDX FirstSibling Function Example

In this example we are going to find the First Children present in the Countries list.


CODE


SELECT
[Measures].[Reseller Sales Amount] ON COLUMNS,
[Geography].[Geography].[Country].[France].FIRSTSIBLING ON ROWS
FROM [Adventure Works]; 

OUTPUT





Analysis:

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

[Measures].[Reseller Sales Amount] ON COLUMNS


Below line of code will check for the France parent (Which is All member) and then finds the first child member of the Country.

[Geography].[Geography].[Country].[France].FIRSTSIBLING


MDX FirstSibling Function Example 2


If we know the Loiret is one of the state in France and our intention is to find the first state present in France then, we can use this FirstSibling function. In this example we are going to find the First Children present in the State Province list and calculate the Reseller Sales Amount of that.


CODE


SELECT
[Measures].[Reseller Sales Amount] ON COLUMNS,
[Geography].[Geography].[State-Province].[Loiret].FIRSTSIBLING ON ROWS
FROM [Adventure Works]; 

OUTPUT





Analysis:

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

[Measures].[Reseller Sales Amount] ON COLUMNS

Below line of code will check for the Loiret parent (Which is France) and then finds the first child member of the France Country.

[Geography].[Geography].[Country].[France].FIRSTSIBLING

For Charente-Maritime State, there is no sales at all so, it is displaying Null results.
MDX FirstSibling Function Alternative


In this example we are going to use the FirstSibling function alternative to achieve the same result. Please refer MDX Parent Function to understand the Parent function and refer MDX FirstChild function to understand the FirstChild function.


CODE


SELECT
[Measures].[Reseller Sales Amount] ON COLUMNS,
[Geography].[Geography].[State-Province].[Loiret].PARENT.FIRSTCHILD ON ROWS
FROM [Adventure Works];


OUTPUT

MDX FirstChild Function

In Multidimensional Expression, FirstChild function will return the First Child member belongs to the specified member. For example, If you know single customer name and if you want to find the Sales of a first customer then you can use this FirstChild function.

TIP: We can use MDX LastChild Function to find the Last Child Member.
MDX FirstChild Function Syntax

The basic syntax of the MDX FirstChild is:

Member_Expression.FIRSTCHILD

Member_Expression: Any Multidimensional Expression that returns valid Member.


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

Following screenshot shows the Countries inside the Geography





Following screenshot shows the [State – Provinces] inside the France Country






MDX FirstChild Function Example

In this example we are going to find the First Children present in the France Country. It means first state present in the France country.


CODE


SELECT
[Measures].[Reseller Sales Amount] ON COLUMNS,
[Geography].[Geography].[Country].[France].FIRSTCHILD ON ROWS
FROM [Adventure Works];


OUTPUT





Analysis:

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

[Measures].[Reseller Sales Amount] ON COLUMNS


Below line of code will write the first child member of the France from all the State-Provices present in the France country.

[Geography].[Geography].[Country].[France].FIRSTCHILD


MDX FirstChild Function Example 2


As we all know Paris is one of the state in France and our intention is to find the first state present in France then, we can use this FirstChild function. In this example we are going to find the First Children present in the State Province list and calculate the Reseller Sales Amount of that.


CODE

SELECT
[Measures].[Reseller Sales Amount] ON COLUMNS,
[Geography].[Geography].[State-Province].[Seine (Paris)].PARENT.FIRSTCHILD ON ROWS
FROM [Adventure Works];


OUTPUT





Analysis:

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

[Measures].[Reseller Sales Amount] ON COLUMNS


From Below line of code,

[Geography].[Geography].[State-Province].[Seine (Paris)].PARENT.FIRSTCHILD


MDX will first implement the Parent function to find the parent member of a [Seine (Paris)], Which is France. Please refer MDX Parent Function for further understanding

Next, It will implement FirstChild function to find the first child member of the France Country, Which is Charente-Maritime. For Charente-Maritime State, there is no sales at all so, it is displaying Null results.

MDX FirstChild Function Alternative


In this example we are going to use the FirstChild function alternative to achieve the same result. Please refer MDX FirstSibling Function to understand the FirstSibling function.


CODE


SELECT
[Measures].[Reseller Sales Amount] ON COLUMNS,
[Geography].[Geography].[State-Province].[Seine (Paris)].FIRSTSIBLING ON ROWS
FROM [Adventure Works];


OUTPUT