Some time you will come across how to get the preceding nth year or forwarding nth year (from hierarchy) from the passed-in parameter value (from attribute hierarchy). System will return error if you ONLY used ParallelPeriod function to pulling out the preceding or forwarding year. This is because the ParallelPeriod function required the member come from the same hierarchy instead of attribute hierarchy.

Here is the MDX code that ONLY make use of ParallelPeriod function:

WITH
MEMBER [nth Year] AS
PARALLELPERIOD([Date].[Calendar].[Calendar Year],
-2,
[Date].[Calendar].[Calendar Year].[CY 2001]
).NAME
SELECT {
[nth Year]
} ON COLUMNS
FROM [Adventure Works]


The above code will return “CY 2003″. This is ONLY valid when member_expression “[Date].[Calendar].[Calendar Year].[CY 2001]” is come from same hierarchy as level_expression “[Date].[Calendar].[Calendar Year]“.

Here is the MDX code that make use LinkMember function to retrieve the preceding or forwarding year with the member between attribute hierarchy and hierarchy dimension:


WITH
MEMBER [nth Year] AS
PARALLELPERIOD([Date].[Calendar].[Calendar Year],
-2,
LinkMember( [Date].[Calendar Year].[CY 2001],
[Date].[Calendar])
).NAME
SELECT {
[nth Year]
} ON COLUMNS
FROM [Adventure Works]


System will return “CY 2003″ although the “[Date].[Calendar Year].[CY 2001]” member come from attribute hierarchy dimension and “[Date].[Calendar].[Calendar Year]” expression_level is come from hierarchy dimension.

Hope you will benefit from the above sample.

To run the sample code, you must install the AdventureWorks sample.