Date Range filter RRS feed

  • Question

  • Hi guys,

    I need to filter result in MDX query by passed Start Date and EndDate

    I come up with following solution

    WHERE (

    FILTER([Dimension1].[ Dimension1].ALLMEMBERS,

    CDate([Dimension1].[ Dimension1].Properties( "Insert Date" )) >= CDate('2/1/2007')  --'2/6/2007'

    AND CDate([Dimension1].[ Dimension1].Properties( "Insert Date" )) <= CDate('2/10/2007')   --'2/7/2007'


    But when I execute script it returns me null for all values in fact table.

    I am sure there is data in specified interval

    Any suggestions are very appreciated.

    What is best approach to filter by date range in MDX query?

    Friday, February 23, 2007 3:55 PM

All replies

  • What service pack are you using? There's been some fixes in SP2 that may apply to this case.
    Friday, February 23, 2007 11:17 PM
  • Actually it worked on SQL 2005 RTM. After I applied SP2 I start getting the empty cells.

    Saturday, February 24, 2007 9:55 AM
  • This is confirmed: It worked fine with SSAS v9.00.247.00   but with SSAS v 9.00.3042.00 return null for all rows and columns

    Does anyone has idea how to make it works?

    Monday, February 26, 2007 1:08 PM
  • Without specifics on what you're doing, it's hard to suggest a specific course of action. (What is the full query, what tool, etc.). Some suggestions on how to proceed:

    • Validate that your filter "set" is in fact correct. I'd look at the the specifics behind the selection, date formatting, etc to ensure it's still being properly parse.
    • Try (in the MDX) creatng a dynamic set, then referencing the set in the where statement. At a minimum, it'll make what you're trying to do easier to understand and test.
    • Depending on your front end tool, consider putting the "WHERE" into the pages (axis 2) instead. It may help you understand how the dimensional references are being resolved.
    Monday, February 26, 2007 3:48 PM