locked
MDX selection between two dates RRS feed

  • Question

  • I need to write MDX query that select data between two dates.
    The problem is that the dates I get from the user are not necessarily exists in my date dimension.

    I tried implementing it by the following code, but it didn't work correctly.
    (The dates in the example : 2008-05, 2009-05 ,are the input dates given by the user, and they are not necessarily exists in the Dim Date table.)
    Here is my code:

    SELECT [Dim_Customer].Members ON COLUMNS,
    [Measures].[DPM] ON ROWS
    FROM [Cube]
    WHERE [Dim Date].[Calendar].[Year Month].[2008-05]:[Dim Date].[Calendar].[Year Month].[2009-05]

    Thanks in advance.
    Tovi
    Tuesday, May 5, 2009 12:12 PM

Answers

  • When the user-selected dates don't exist in your [Date] dimension, how should the date range be determined? Assuming that the Date dimension has no gaps / holes, then one way to determine the start date would be the first date that is >= the user selected date.

    Here's one approach: suppose the value of [Dim Date].[Calendar].[Year Month] members is configured to be the date of the first day (as in Adventure Works), and let the user selections be represented by string parameters which are also the first day of the month: BegMonth and EndMonth (in your example, "2008-05-01" and "2009-05-01"). Then this sample Adventure Works query shows the approach:
    With
    Member [Measures].[BegMonth] as "2004-01-01"
    Member [Measures].[EndMonth] as "2005-01-01"
    select
    [Measures].[Sales Amount] on 0
    from [Adventure Works]
    where {[Date].[Calendar].[Month].Item(
    iif([Date].[Calendar].[Month].Item(0).MemberValue >=
    CDate([Measures].[BegMonth]), 0,
    DateDiff("m", 
    [Date].[Calendar].[Month].Item(0).MemberValue,
    CDate([Measures].[BegMonth])))) :
    [Date].[Calendar].[Month].Item(
    iif(Tail([Date].[Calendar].[Month]).Item(0).MemberValue <=
    CDate([Measures].[EndMonth]), 0,
    DateDiff("m",
    CDate([Measures].[EndMonth]),
    [Date].[Calendar].[Month].Item(0).MemberValue)))}
    ----------------------------------------------------------
    Sales Amount
    $25,808,962.34

     

     

     


    - Deepak
    Tuesday, May 5, 2009 4:39 PM