locked
Date range problem in MDX query RRS feed

  • Question

  • Hi,

    I am trying to filter result based on date range in where clause. I used following query:

    select {[Measures].[Quantity],
    [Measures].[Sales} on columns ,
    [Date].[Calendar].[Date] on rows from [Sales]
    where {[Date].[Date].[01 Jan 2008]:[Date].[Date].[20 Nov 2009]} -- Date is attribute

    This works fine as long as the from date (01 Jan 2008) exist in Date dimension(date dimension has date from 01 Jan 2007 to 31 Dec 2009). But when the date value specified in date range doesn't exist (like 01 Jan 2000) in the date dimension, it returns null values for measures.
    My requirement is to display valid measure values even if user specifies date like 01 Jan 2000 in "from" date range in where clause. How do I resolve this issue?
    Thanks in advance for your help and input.
    Sunday, November 29, 2009 9:33 PM

Answers

  • Hi,

     

    I think it should return valid values even if the member doesn't exist by default. I’m not sure why you see the wrong results. For example:

    select [Measures].[Internet Sales Amount] on 0,

    [Date].[Calendar].[Date] on 1

    from [Adventure Works]

    where {[Date].[Date].&[-asdf]:[Date].[Date].&[13]}-- Date is attribute

    The member Date].[Date].&[-asdf] doesn't exist, but it still return valid value from the beginning to the [Date].[Date].&[13]. I mean the above query equal to:

    select [Measures].[Internet Sales Amount] on 0,

    [Date].[Calendar].[Date] on 1

    from [Adventure Works]

    where {null:[Date].[Date].&[13]}-- Date is attribute

     

    Returning all null values only occurs when both start member and end member are not valid:

    select [Measures].[Internet Sales Amount] on 0,

    [Date].[Calendar].[Date] on 1

    from [Adventure Works]

    where {[Date].[Date].&[-asdf]:[Date].[Date].&[fdas]}

    Or

    select [Measures].[Internet Sales Amount] on 0,

    [Date].[Calendar].[Date] on 1

    from [Adventure Works]

    where {null:[Date].[Date].[13]}-- there is not a member [Date].[Date].[13]

     

    So, please check the member [Date].[Date].[20 Nov 2009] with below query:

    with member measures.x as [Date].[Date].[20 Nov 2009].name

    select measures.x on columns

    from [Sales];

    Or it should be: [Date].[Date].&[20 Nov 2009] ?

     

    Hope this helps.

    Raymond

    • Marked as answer by Raymond-Lee Friday, December 11, 2009 6:46 AM
    Tuesday, December 1, 2009 3:46 AM
  • Raymond, thanks for input. I tried in the same way. When start member exist and end member does't exit or start member doesn't exist and end member exist, it returned result. But when start member and end member don't exist, it returned null. I re-wrote the query as follows and it fixed the issue I was having date range in where clause.

    select {[Measures].[Quantity], [Measures].[Sales]} on columns,

    (filter([Date].[Date].[All].children, CDate([Date].[Date].Item(0).MemberValue) >= CDate("2000-01-01")

    and

     

     

    CDate([Date].[Date].Item(0).MemberValue) <= CDate("2015-12-31") )) on rows from [Sales]


    Above query returns correct result even if start date and end date don't exist.


    Thanks,




    • Marked as answer by Raymond-Lee Friday, December 11, 2009 6:46 AM
    Wednesday, December 2, 2009 12:42 AM

All replies

  • ".. even if user specifies date like 01 Jan 2000 in "from" date range .." - what client tool is being used, and is the MDX query paramterized? Maybe it's Reporting Services with Date paramtere, because with OLAP client tools like Excel, the user only selects from valid dimension attribute members.
    - Deepak
    Sunday, November 29, 2009 11:01 PM
  • I am using SQL Server Management Studio to execute the MDX. I am executing the query without any parameter as shown in my original posting. If start date before min date in Date dimension, it returns null for Quantity and Sales measures (all values). But if start date lies within Date dimension values, it returns right values for the measures. Is there any thing wrong?

    Thanks
    Monday, November 30, 2009 5:25 AM
  • Hi,

     

    I think it should return valid values even if the member doesn't exist by default. I’m not sure why you see the wrong results. For example:

    select [Measures].[Internet Sales Amount] on 0,

    [Date].[Calendar].[Date] on 1

    from [Adventure Works]

    where {[Date].[Date].&[-asdf]:[Date].[Date].&[13]}-- Date is attribute

    The member Date].[Date].&[-asdf] doesn't exist, but it still return valid value from the beginning to the [Date].[Date].&[13]. I mean the above query equal to:

    select [Measures].[Internet Sales Amount] on 0,

    [Date].[Calendar].[Date] on 1

    from [Adventure Works]

    where {null:[Date].[Date].&[13]}-- Date is attribute

     

    Returning all null values only occurs when both start member and end member are not valid:

    select [Measures].[Internet Sales Amount] on 0,

    [Date].[Calendar].[Date] on 1

    from [Adventure Works]

    where {[Date].[Date].&[-asdf]:[Date].[Date].&[fdas]}

    Or

    select [Measures].[Internet Sales Amount] on 0,

    [Date].[Calendar].[Date] on 1

    from [Adventure Works]

    where {null:[Date].[Date].[13]}-- there is not a member [Date].[Date].[13]

     

    So, please check the member [Date].[Date].[20 Nov 2009] with below query:

    with member measures.x as [Date].[Date].[20 Nov 2009].name

    select measures.x on columns

    from [Sales];

    Or it should be: [Date].[Date].&[20 Nov 2009] ?

     

    Hope this helps.

    Raymond

    • Marked as answer by Raymond-Lee Friday, December 11, 2009 6:46 AM
    Tuesday, December 1, 2009 3:46 AM
  • Raymond, thanks for input. I tried in the same way. When start member exist and end member does't exit or start member doesn't exist and end member exist, it returned result. But when start member and end member don't exist, it returned null. I re-wrote the query as follows and it fixed the issue I was having date range in where clause.

    select {[Measures].[Quantity], [Measures].[Sales]} on columns,

    (filter([Date].[Date].[All].children, CDate([Date].[Date].Item(0).MemberValue) >= CDate("2000-01-01")

    and

     

     

    CDate([Date].[Date].Item(0).MemberValue) <= CDate("2015-12-31") )) on rows from [Sales]


    Above query returns correct result even if start date and end date don't exist.


    Thanks,




    • Marked as answer by Raymond-Lee Friday, December 11, 2009 6:46 AM
    Wednesday, December 2, 2009 12:42 AM