In MDX, the Cousin function will write the same level member under the Ancestor specified in the second parameter. For example, If you know the value of the first Quarter in Calendar year 2014. And you want to find the sales of the last Quarter in the Calendar year 2015. Then you can use this Cousin function.
MDX Cousin Function Syntax
The basic syntax of the Cousin is:
COUSIN( Member_Expression, Ancestor_Member_Expression )
- Member_Expression: Any Multidimensional Expression that returns valid Member. It will find the member Level. For instance, whether the member is the first month of a quarter, semester or Year, etc.
- Ancestor_Member_Expression: Any Multidimensional Expression that returns valid Ancestor Member. The MDX Cousin function will find the Cousin of First argument present in this Ancestor_Member.
In this article, we will show you how to write the Cousin function in the query with examples. For this, We are going to use the below show data
MDX Cousin Function Example
In this example, we are going to use Cousin function to find the cousin of Q1 CY 2012 in Calendar Year 2013
SELECT { [Date].[Calendar].[Calendar Quarter].[Q1 CY 2012], COUSIN ( [Date].[Calendar].[Calendar Quarter].[Q1 CY 2012], [Date].[Calendar].[Calendar Year].[CY 2013] ) } ON COLUMNS, [Measures].[Internet Sales Amount] ON ROWS FROM [Adventure Works]
In the above MDX Query, We used [Internet Sales amount] on the Rows
[Measures].[Internet Sales Amount] ON ROWS
The first line of code after the SELECT will check for Q1 CY 2012 and write the Internet sales amount of Q1 CY 2012. As we all know, Q1 CY 2012 is the First Quarter in Calendar year 2012.
[Date].[Calendar].[Calendar Quarter].[Q1 CY 2012],
Below line of MDX cousin code will check for the First Quarter in the Calendar year 2013( because Q1 CY 2012 is the First Quarter in the Calendar year 2012) and then write the Internet sales amount of Q1 CY 2013
COUSIN ( [Date].[Calendar].[Calendar Quarter].[Q1 CY 2012], [Date].[Calendar].[Calendar Year].[CY 2013] )
MDX Cousin Function Example 2
In this example, we are going to use Cousin function to find the cousin of January 2012 in the Fourth Quarter of Calendar Year 2013
SELECT { [Date].[Calendar].[Month].[January 2012], COUSIN ( [Date].[Calendar].[Month].[January 2012], [Date].[Calendar].[Calendar Quarter].[Q4 CY 2013] ) } ON COLUMNS, [Measures].[Internet Sales Amount] ON ROWS FROM [Adventure Works]
In the above MDX Query, We used [Internet Sales amount] on the Rows
[Measures].[Internet Sales Amount] ON ROWS
The first line of MDX Cousin code after the SELECT will check for January 2012 Month and write the Internet sales amount of January 2012. As we all know, January is the First Month of the Q1 CY 2012 in the Calendar year 2012.
[Date].[Calendar].[Month].[January 2012],
Below line of code will check for the First Month of the Calendar Quarter Q4 CY 2013 (i.e., October) and then write the Internet sales amount of October 2013
COUSIN ( [Date].[Calendar].[Month].[January 2012], [Date].[Calendar].[Calendar Quarter].[Q4 CY 2013] )
NOTE: If we replace the Calendar Quarter from the above lines of code with Calendar semester 2, MDX Query will write the First month in semester 2
Finding Range Using MDX Cousin Function
In this example, we are going to calculate the Range between one point to another using the Cousin Function.
SELECT [Measures].[Internet Sales Amount] ON COLUMNS, { [Date].[Calendar].[Month].[January 2012]: COUSIN ( [Date].[Calendar].[Month].[January 2012], [Date].[Calendar].[Calendar Quarter].[Q2 CY 2013] ) } ON ROWS FROM [Adventure Works]
The first line of code after the SELECT will check for January 2012 Month and write the Internet sales amount of January 2012. As we all know, January is the First Month of the Q1 CY 2012 in the Calendar year 2012.
[Date].[Calendar].[Month].[January 2012],
Below line of cousin code will check for the First Month of the Calendar Quarter Q2 CY 2013 (i.e., April)
COUSIN ( [Date].[Calendar].[Month].[January 2012], [Date].[Calendar].[Calendar Quarter].[Q2 CY 2013] )
Now the range Operator (:) between the above statements will write all the Calender Month from January 2012 to April 2013.