US and Worldwide: +1 (866) 660-7555
Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: Accessing the current date in MDX

  1. #1
    Join Date
    Nov 1999
    Posts
    1,619

    Default Accessing the current date in MDX

    When someone runs a report, they typically want to see only current data -- data from the year to date, the previous quarter, and so forth. Currently that's not very easy to achieve with Mondrian, because Mondrian doesn't provide functions to get the current time, and there's no way to override the default member of the time dimension.
    I'd like to find out two things: How do you currently achieve this? And what features could be added to Mondrian to make this easier?
    I have some ideas, but I'd like to hear what the community has to say first. If you have experience doing this in other MDX providers such as MSAS, I'd like to hear that, too -- because I prefer to comply with standards than make my own.

  2. #2
    Join Date
    May 2002
    Posts
    7

    Default RE: Accessing the current date in MDX

    Looks like the formatting code can handle an object of type Date, but
    > there is not mondrian.olap.type.DateType so a user defined functions
    > (such as the Mondrian version of MS's vb Now() function) can
    > not return
    > a Date.
    >
    > Is there a Date type in MDX?
    >
    > I found the following on the web:
    >
    > StrToMember("[Time].[Month].[" + Format(Now(), "mmmm") + "]")
    >
    > as a way of getting the current month.
    >
    > Richard

  3. #3

    Default RE: Accessing the current date in MDX

    Hello, I'm very interested in this subject.
    Julian, you said you had some ideas. I would like to read about them.
    I've been trying to find the way to do this in Mondrian and didn't get the right one.
    Thanks a lot,
    --Claudio

  4. #4
    Join Date
    Nov 1999
    Posts
    1,619

    Default RE: Accessing the current date in MDX

    This problem could be solved using a user-defined function:
    CurrentDate(<Hierarchy>, <FormatString>) returns <Member>
    for example,
    CurrentDate([Time], "[Time].[%YYYYY].[%MM].[%dd]")
    would return a member such as
    [Time].[2005].[09].[29]
    If someone would like to write such a function (plus a few unit tests) I would be delighted to include it in Mondrian.

  5. #5
    Join Date
    Oct 2005
    Posts
    2

    Default RE: Accessing the current date in MDX

    IMHO, suitable function name is LatestDate(...)
    In our case customers most often want not a current date, but the last one with data available - last available day/week/month/quarter/year data, which could be yesterday or few month ago.
    Anyway - i can try to create such function(s) and may other if I'll be able to resolve perforce+firewall problems
    -- anatoly

  6. #6
    Join Date
    Oct 2005
    Posts
    2

    Default RE: Accessing the current date in MDX

    Ooops.... I was assume LastPeriods() function, sorry.
    -- anatoly

  7. #7

    Default RE: Accessing the current date in MDX

    For CurrentDate(<Hierarchy>, <FormatString>) returns <Member>, should we check if the returned member exists in cube (underlying database)?

  8. #8
    Join Date
    Mar 2003
    Posts
    2

    Default RE: Accessing the current date in MDX

    What about a third parameter:
    CurrentDate(<Hierarchy>, <FormatString>, <Find>)
    <Find>
    0 = return exact member if exists, else return NULL
    -1 = if exact member does not exist return closest available member in the past
    1 = if exact member does not exist return closest available member in the future
    Ralf

  9. #9
    Join Date
    Nov 1999
    Posts
    1,619

    Default RE: Accessing the current date in MDX

    The <Find> parameter is a great idea. I would make it optional, and use symbols EXACT, AFTER, BEFORE:
    CurrentDate(<Hierarchy>, <FormatString>[, <Find>])
    <Find>
    EXACT = return exact member if exists,
    else return NULL
    BEFORE = if exact member does not exist return
    closest available member in the past
    AFTER = if exact member does not exist return
    closest available member in the future

  10. #10

    Default RE: Accessing the current date in MDX

    I don't known if anything (besides YTD, MTD, ...) has be made up till know (or upcoming release) ?
    I need to get today, yesterday, this week, last week, this month, last month, 2 weeks ago (only the data of 1 week) and so on.
    At this moment, is there anything I can do to solve this using mondrian ?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •