DateToMember

The MDX DateToMember function for Essbase returns the date-time dimension member specified by the input date and the input layer.

Syntax

DateToMember ( date, dimension [,layer])

Parameters

date

A number representing the input date between January 1, 1970 and Dec 31, 2037. The number is the number of seconds elapsed since midnight, January 1, 1970. To retrieve this number, use any of the following functions: Today(), TodateEx(), GetFirstDate(), GetLastDate(), DateRoll().

Date-time attribute properties of a member can also be used to retrieve this number. For example,

• Product.currentmember.[Intro Date] returns the product introduction date for the current product in context.

• [Cola].[Intro Date] returns the product introduction date for Cola.

dimension

A date-time dimension specification.

layer

Optional. A date-time dimension layer specification. If not specified, defaults to the date-time dimension's leaf generation.

Notes

• This function is applicable only to aggregate storage databases.

• This function is only applicable if there is a date-time dimension in the outline.

Example

Consider the following Time-Date dimension hierarchy:

Time dimension (gen 1)
   Years (gen 2)
        Semesters (gen 3)
           Quarters (gen 4)
                Months (gen 5)
                     Weeks (gen 6)
                          Days (gen 7)

The following query returns sales for the week containing Dec 25, 2006 for the product Cola in the market California.

SELECT 
{Sales} ON COLUMNS,
{
DateToMember(
 TodateEx("Mon dd yyyy", "December 25 2006"), 
 [Time dimension].Dimension, 
 [Time dimension].[Weeks])
 } ON ROWS
FROM MySamp.Basic
WHERE (Actual, California, Cola);