MDX PeriodsToDate, a more efficient function

Prerequisite: installation of Analysis Services Stored Procedure Project

The following are examples for GetEfficientPeriodsToDateSet a more efficient stored procedure to get what you get from PeriodsToDate.

If you run the following MDX query you are aiming to get the [Normal] measure for all dates starting from 2012-01-01 to 2012-05-08:

with member [y] as CSTR([D Date].[YEAR].CurrentMember.Name)

member [m] as CSTR([D Date].[month no].CurrentMember.Name)

member [d] as CSTR([D Date].[day].CurrentMember.Name)

select {[Measures].[Normal],[y],[m],[d]} on 0,

PeriodsToDate([D Date].[Hierarchy].[YEAR],[D Date].[Hierarchy].[YEAR].[2012].[5].[8]) on 1

from [TC Cube]

the following is part of the result, and it is 130 rows, each row represents a day in 2012 from 2012-01-01 to 2012-05-08

but look at the result of the following MDX query which uses ASSP.GetEfficientPeriodsToDateSet function:

with member [y] as CSTR([D Date].[YEAR].CurrentMember.Name)

member [m] as CSTR([D Date].[month no].CurrentMember.Name)

member [d] as CSTR([D Date].[day].CurrentMember.Name)

select {[Measures].[Normal],[y],[m],[d]} on 0,

ASSP.GetEfficientPeriodsToDateSet("Year",[D Date].[Hierarchy].[YEAR].[2012].[5].[8]) on 1

from [TC Cube]

This time the result is just 13 rows, the first four rows represents the first four months of 2012 and [Normal] for those rows is the total for each month, the reset represents the days of month 5 of 2012 till 2012-05-08

Even though there is a big difference between the number of rows resulted from the first query and those from the second one, but if you compute the total of [Normal] you will find it is the same  and equals 293640.

The benefit of ASSP.GetEfficientPeriodsToDateSet is clear when used in calculated measure. generating the result is more faster and efficient; as you have to iterate over 13 rows (in this case) instead of 130.

By the way GetEfficientPeriodsToDateSet function has a very similar signature to the PeriodsToDate MDX function. The first parameter (LevelName) is a string specifying the level name. The second parameter (MemberExpression) is a valid MDX member expression.
Another example on this function could be getting all dates till 2012-05-08:

with member [y] as CSTR([D Date].[YEAR].CurrentMember.Name)

member [m] as CSTR([D Date].[month no].CurrentMember.Name)

member [d] as CSTR([D Date].[day].CurrentMember.Name)

select {[Measures].[Normal],[y],[m],[d]} on 0,

ASSP.GetEfficientPeriodsToDateSet("(All)",[D Date].[Hierarchy].[YEAR].[2012].[5].[8]) on 1

from [TC Cube]

More signature can be found in:
http://asstoredprocedures.codeplex.com/wikipage?title=EfficientToDate&referringTitle=Home