Post reply

MDX Date range filter query not working

  • February 6, 2015 at 5:11 pm

    #315077

    Hello Friends,

    We have a Date dimension like the one in AdventureWorks sample cube in our Cube.

    But the MDX used to filter date isn't working and the same thing works for AdventureWorks cube. I just wanted to know what design changes need to be done in Date Dimension for it to work :

    MDX Query :

    SELECT FILTER([Dim Date].[Full Date].LEVELS(1).MEMBERS, ([Dim Date].[Full Date].CURRENTMEMBER.MEMBERVALUE >= CDATE("2/6/2000") AND [Dim Date].[Full Date].CURRENTMEMBER.MEMBERVALUE <= CDATE("12/12/2020"))) ON 0 FROM [Super Dw]

    if i remove the part, 'LEVELS(1)' from above query it works correctly.

    I can't change the query as it's being fired by a tool so i need to change the design for it to work 🙂

    Thanks,

    Amar

    BI Developer
    SSRS, SSIS, SSAS, IBM Cognos, IBM Infosphere Cubing services, Crystal reports, IBM DB2, SQL Server, T-SQL
    Please visit... ApplyBI

  • February 18, 2015 at 4:02 pm

    #1778506

    Hi H4K,

    The Levels function to which you refer is zero based, meaning that LEVELS(1) is the second level in the hierarchy. Can you try adding another level to the hierarchy below full date?

    SQL SERVER Central Forum Etiquette[/url]

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply