MDX Essentials - MDX Time Series Functions, Part I: PeriodsToDate() and Kindred Functions - Page 2

The PeriodsToDate() Function

The PeriodsToDate() function, according to the Analysis Services Books Online, "returns a set of periods (members) from a specified level starting with the first period and ending with a specified member." Within the scope of a level that is specified within the function, the function returns a set of periods that exist on the same level as the member that is also specified in the expression. In other words, it returns the number of periods within the specified level, up to and including the specified member. When no level or member is specified, the value of the member is Time.CurrentMember (a concept we have discussed earlier in our series), with the Level value being the parent level of Time.CurrentMember.

Discussion

The PeriodsToDate() function allows us to meet very common business needs, including the calculation of a year-to-date total. The calculation of this total requires accumulation over a range of members of the Time dimension. This set of time members is easily assembled using the PeriodsToDate() function, although other, less direct approaches exist to meet this requirement.

Syntax

Syntactically, the source member is placed within the parentheses to the right of PeriodsToDate, as shown in the following illustration:

PeriodsToDate([«Level»[, «Member»]])

The function returns the number of periods within the «Level», up to and including «Member». The following simplified example expression would return the twelve months of year 1998:

PeriodsToDate(Year, [Time].[1998].[12]

In the above, 12 is the month of December as it exists in the HR sample cube, which is provided with the typical Analysis Services installation. The members returned using the above expression within a query would be the same as those returned using the following range expression:

Time.[1998].[1]: Time.[1998].[12]

In the above, much as we see in other languages, the colon operator denotes a range; in our case, the range is members of the Time dimension, Year 1998, Month level, members 1 (January) through 12 (December).

Let's look at a more involved example, using a calculated member as the means to our ends. As we saw in our last lesson, a calculated member is an excellent vehicle for familiarizing ourselves with the syntax.

WITH

   MEMBER [Measures].[YTD Salaries] AS
   'Sum(PeriodsToDate([Time].[Year]),[Org Salary])'

SELECT
 
   {[Measures].[Org Salary],[Measures].[YTD Salaries]} ON COLUMNS,
     [Time].[Quarter].Members ON ROWS

FROM HR

The result dataset returned would appear as shown in Illustration 2:

For a review of what we learned in the previous lesson about calculated members, we need look no further than the above example. The WITH section contains the definition of the calculated member / measure YTD Salaries. We note, as well, that the PeriodsToDate() function is used, together with, and within the context of, the Sum statement; all are used within the definition of the calculated member. Let's "parse" the action here into plain English, as the combination we see above is certainly typical enough in the real world.

First, the YTD Salaries measure is being defined, via the Sum statement, as the "sum of a numeric expression evaluated over a set." The syntax of a simple sum statement is relatively straightforward, and might be represented as follows:

Sum((«Set»[, «Numeric Expression»]))

The Org Salary measure inhabits the Numeric Expression portion of the Sum statement. The Numeric Expression portion of the Sum function in effect serves as the column axis in the case of our calculated measure. We are asking for Org Salary to be displayed on the column axis, and all Quarter-level members of the Time dimension (which, in the HR sample cube, are the quarters of 1997 and 1998 only) to be displayed on the rows axis.

The first half, or Set portion of the Sum statement (in our example (PeriodsToDate([Time].[Year])) serves as the row axis for a subquery that is generated by the Sum function. When all is done, its effect is to ask for a set of members to be returned a set of members that we can use in the Sum function as a basis for accumulation.

In addition to selecting Org Salary as a component in our function, we bring it into our query as the standard measure ([Measures].[Org Salary]); this is done to illustrate the actual quarterly totals, so that we can grasp the effects of the Sum function visually. It's easy to see what is happening, as the YTD Salaries calculated measure obviously increases each quarter by the amount of the quarterly Org Salary expense, illustrating the cumulative effect that is brought about through our use of the PeriodsToDate() function as the Set portion of the Sum statement. We note, too, that the accumulated YTD Salaries value "resets" at the first quarter of 1998, because our scope is defined as Year.