MDX #3 – Year-Over-Year Comparison within Given Period

Requirement: Users specify a date range by giving a Start Date and a End Date, and expect to make Year-Over-Year comparison of a measure (e.g. Sales Amount) in the given period. For example, a user input start date as “04/07/2007” and end date as “04/11/2007”, and want to compare the sales between the period “04/07/2007 – 04/11/2007” and the same period in last year “04/07/2006 – 04/11/2006”.

MDX_01

First, we need get the data range using the range operator ‘:’ on the given start date and end date, and aggregate the sales occurred within that period.

MEMBER [Measures].[Sales in Given Period] AS
AGGREGATE (
{STRTOMEMBER(@StartDate, CONSTRAINED) : STRTOMEMBER(@EndDate, CONSTRAINED) }
, [Measures].[Reseller Sales Amount]
)

Then we could use PARALLELPERIOD to get the same period in last year. Please note that we could use range operator within a PARALLELPERIOD function, so we have to separately PARALLELPERIOD the start date and the end date, and use range operator afterwards.

MEMBER [Measures].[Sales in Given Period Last Year] AS
AGGREGATE (
{
PARALLELPERIOD(
[Date].[Calendar].[Calendar Year]
,1
,STRTOMEMBER("[Date].[Calendar].[Date].&[20060211]")
):
PARALLELPERIOD(
[Date].[Calendar].[Calendar Year]
,1
,STRTOMEMBER("[Date].[Calendar].[Date].&[20060911]")
)
}
, [Measures].[Reseller Sales Amount]
)

Below is the complete script:

WITH
MEMBER [Measures].[Sales in Given Period] AS
AGGREGATE (
{STRTOMEMBER(@StartDate, CONSTRAINED) : STRTOMEMBER(@EndDate, CONSTRAINED) }
, [Measures].[Reseller Sales Amount]
)

MEMBER [Measures].[Sales in Given Period Last Year] AS
AGGREGATE (
{
PARALLELPERIOD(
[Date].[Calendar].[Calendar Year]
,1
,STRTOMEMBER(@StartDate, CONSTRAINED)
):
PARALLELPERIOD(
[Date].[Calendar].[Calendar Year]
,1
,STRTOMEMBER(@EndDate,  CONSTRAINED)
)
}
, [Measures].[Reseller Sales Amount]
)

SELECT {
[Measures].[Sales in Given Period],
[Measures].[Sales in Given Period Last Year]
} ON COLUMNS,
{
[Product].[Product Categories].[Category]
} ON ROWS
FROM [Adventure Works]

Tip: when using STRTOMEMBER function to convert the data string into Member value, the data string has to be in correct format. We could directly pass data parameter into MDX query and build the data string to the correct format in the MDX script. However, a better way is to build the data string at SSRS report level (using expression in the parameter of dataset) as the snapshot shown below:

MDX_02

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s