Skip to main content
************* PRERELEASE CONTENT *************
Previous sectionNext section

PERIODSTODATE

Returns the set of child or descendent members of the given level, up to and including the given member. This function is intended primarily for use with time levels.

Returned Type

This function returns a set of members.

Syntax and Details

PERIODSTODATE(ancestor_level,member_expression)

Where:

  • ancestor_level is an expression that returns a level. For example:

    [dimension_name].[hierarchy_name].[level_name]
    Copy code to clipboard

    This level must be either an ancestor to the member given by member_expression.

  • member_expression is an expression that returns a member.

    This expression cannot refer to a measure.

Given a level and a member, this function returns a set that consists of a range of members, from the first member that is a descendent of the given level, up to and including the given member. The members are in the default order specified in the level definition in the cube.

Example

The following query displays all quarters up to Q3 2003, within the year:

SELECT MEASURES.[%COUNT] ON 0, PERIODSTODATE(birthd.year,birthd.[Q3 2003]) ON 1 FROM patients
 
                             Patient Count
1 Q1 2003                                35
2 Q2 2003                                44
3 Q3 2003                                43
Copy code to clipboard

In contrast, the following query displays all quarters up to Q3 2003, within the decade:

SELECT MEASURES.[%COUNT] ON 0, PERIODSTODATE(birthd.decade,birthd.[Q3 2003]) ON 1 FROM patients
 
                             Patient Count
 1 Q1 2000                               33
 2 Q2 2000                               33
 3 Q3 2000                               45
 4 Q4 2000                               39
 5 Q1 2001                               37
 6 Q2 2001                               40
 7 Q3 2001                               36
 8 Q4 2001                               37
 9 Q1 2002                               39
10 Q2 2002                               35
11 Q3 2002                               40
12 Q4 2002                               38
13 Q1 2003                               35
14 Q2 2003                               44
15 Q3 2003                               43
Copy code to clipboard

This function returns a set expression in the form of a range of members. That is, for example, the following two expressions are equivalent:

PERIODSTODATE(birthd.decade,birthd.[Q3 2003])
birthd.[Q1 2000]:birthd.[Q3 2003]
Copy code to clipboard

See Also