Date filter in MDX

Hi guys,

I have a cube where I want to make filter based on date range. I've created a cube with appropriate measures and I have Dimension with date field ( I want to filter on that field).

I've found two solutions:

FILTER([Reservation Search Log].[Reservation Search Log].ALLMEMBERS,
CDate([Reservation Search Log].[Reservation Search Log].Properties( "Simple Insert Date" ))>= "2/6/2007" --'2/6/2007'
AND CDate([Reservation Search Log].[Reservation Search Log].Properties( "Simple Insert Date" )) <= "2/7/2007" --'2/7/2007'

but this stopped working after I applied SP2 - i just get null values for all measures

and this one

[Reservation Search Log].[Simple Insert Date].&[February 1, 2007] :
[Reservation Search Log].[Simple Insert Date].&[February 10, 2007]

but in I would like to pass these dates as parameters. and if both dates are outside the range I get no results. In real case I should get all rows like this
data in SSAS February 1, 2007 to February 10, 2007
I pass January 1, 2007 to March 10, 2007
and no result are returned

If there is no appropriate records for both date parameters no rows are returned...

Any ideas are highly appreciated.Filter is not really an efficient way to do this, if possible I usually try to populate drop down lists of members in reports, but sometimes this is not always practical. Have you tried converting your comparison values to dates as well?

FILTER([Reservation Search Log].[Reservation Search Log].ALLMEMBERS,
CDate([Reservation Search Log].[Reservation Search Log].Properties( "Simple Insert Date" ))>= CDate("2/6/2007") --'2/6/2007'
AND CDate([Reservation Search Log].[Reservation Search Log].Properties( "Simple Insert Date" )) <= CDate("2/7/2007") --'2/7/2007'|||Hi Darren,

Thank very much for helping me with this....

I don't get what do you mean with converting comparison values to dates...

the example I posted is from real MDX query in which I got that doesn't work on SP2 and both sides are converted using CDate before comparison is made

Any other ideas? What is the best way to achieve such goal?|||

The sample you posted only had one side converted to a date, the other was a string value. The example you posted has

>= "2/6/2007"

as the first comparison, which is a comparson to a string value where as if you look at what suggested in my last post it was to use something like:

>= CDATE("2/6/2007")

I tested this in SP2 and it works for me.

|||

this is interesting...

I had this filter clause in where clause and I had the (null) problem...

I moved filter clause in select in subquery and everything is fine now...

Sorry for missing the changes in your post. I've made so many tests and I thought I posted same as you

Thanks a lot, Darren