none
Date ranges MDX

    Question

  • Apologies if this is a common post.

    I'm fairly new to MDX and would like help in what is probably quite simple

    I have a dimension table with two dates, fromDate and toDate.  I then would to return members (lets say Projects) between those dates using a date range in the slicer which will be set to Quarterly.

    This is a SQL example:

    WHERE toDate >= dimDate and fromDate <= dimDate

    i.e.

    WHERE toDate >= '1 Jan 13' and fromDate <= 31 Mar 13'

    The reason the toDate should be >= is because there will be multiple dates for the Projects and so multiple rows in the dimension table. (that's not so important)

    I'd like to avoid deviating from the above MDX translation.  If anyone could help, of course, I'll give as much kudos as I can:)

    Wednesday, November 06, 2013 9:44 PM

Answers

  • Hi Cidr,

    From your description, you want to select the data on a date range, right? If in this case, please try the query below.

    select
    {[Measures].[Internet Sales Amount]
    } on columns,
    {[Date].[Date].members} on rows
    from(
    select
    (
    STRTOMEMBER("[Date].[Date].&["+@StartDate+"]"):STRTOMEMBER("[Date].[Date].&["+@EndDate+"]")
    ) on columns
    from [Adventure Works]
    ) 
    


    Reference:StrToMember (MDX)

    Regards,


    Charlie Liao
    TechNet Community Support

    Friday, November 08, 2013 9:55 AM
    Moderator
  • This is slight modification to above query.

    The above query works if you are using standalone date parameter without assigning any available/default values. If you are assigning a MDX query result to your fromdate and todate, you can give the range in your MDX query easily like below. And you need to map those parameters to this dataset.

    select
    {[Measures].[Internet Sales Amount]
    } on columns,
    {[Date].[Date].members} on rows
    from(
    select
    (
    STRTOMEMBER(@StartDate):STRTOMEMBER(@EndDate)
    ) on columns
    from [Adventure Works]

    Friday, November 08, 2013 12:07 PM

All replies

  • Hi

    It is very simple to implement, RANGE (:) operator will give the same function as you required

    Please refer to this

    http://technet.microsoft.com/en-us/library/ms146001.aspx


    Prav

    Wednesday, November 06, 2013 10:12 PM
  • Thanks Prav,

    I've looked at the technique.  I'm unsure how I'd implement this (sorry for asking to spell it out as I'm new to MDX).

    I'm not sure if this is what I'm looking for.  Does this involve a date range (from and to date on the slicer) and two dates on a dimension?

    Thanks

    Thursday, November 07, 2013 12:41 AM
  • Hi Cidr,

    From your description, you want to select the data on a date range, right? If in this case, please try the query below.

    select
    {[Measures].[Internet Sales Amount]
    } on columns,
    {[Date].[Date].members} on rows
    from(
    select
    (
    STRTOMEMBER("[Date].[Date].&["+@StartDate+"]"):STRTOMEMBER("[Date].[Date].&["+@EndDate+"]")
    ) on columns
    from [Adventure Works]
    ) 
    


    Reference:StrToMember (MDX)

    Regards,


    Charlie Liao
    TechNet Community Support

    Friday, November 08, 2013 9:55 AM
    Moderator
  • This is slight modification to above query.

    The above query works if you are using standalone date parameter without assigning any available/default values. If you are assigning a MDX query result to your fromdate and todate, you can give the range in your MDX query easily like below. And you need to map those parameters to this dataset.

    select
    {[Measures].[Internet Sales Amount]
    } on columns,
    {[Date].[Date].members} on rows
    from(
    select
    (
    STRTOMEMBER(@StartDate):STRTOMEMBER(@EndDate)
    ) on columns
    from [Adventure Works]

    Friday, November 08, 2013 12:07 PM