PERIODSTODATE
Returned Type
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 clipboardThis 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
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
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]