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

Date Context

How can I get two measures in the same query with different date contexts?

For example, lets say I have two measures, Payment, Taxes and Amount. This query would get me total amount for both measures for 2006.

SELECT {Measures.Payment, Measures.Amount, Measures.Taxes} ON COLUMNS, {Company.Name} ON ROWS FROM MyCube WHERE Date.Year.2006

How can I get the total Payment for 2006, 2007, and 2008 while leaving the generic query date context which is 2006? Thanks in advance.

There are two options, you could specify the required dates using sets of tuples in your query

SELECT {(Date.Year.CurrentMember,Measures.Payment)
, (Date.Year.CurrentMember,Measures.Amount)
, (Date.Year.CurrentMember,Measures.Taxes)
(Date.Year.2007,Measures.Payment)
(Date.Year.2008,Measures.Payment)
} ON COLUMNS,
{Company.Name} ON ROWS
FROM MyCube
WHERE Date.Year.2006

Or you could create a couple of calculated members to return the specified dates, You could even make these calculations a bit more generic and put them in the cubes calculation script.

WITH
MEMBER Measures.PrevYrPayment AS (Date.Year.CurrentMember.Lag(1),Measures.Payment)
MEMBER Measures.PrevYr2Payment AS (Date.Year.CurrentMember.Lag(2),Measures.Payment)
SELECT {Measures.Payment
, Measures.Amount
, Measures.Taxes
,Measures.PrevYrPayment
,Measures.PrevYr2Payment
} ON COLUMNS,
{Company.Name} ON ROWS
FROM MyCube
WHERE Date.Year.2006

|||

Thanks for your reply.

The problem is more like this:

SELECT {Measures.Payment, Measures.Amount, Measures.Taxes} ON COLUMNS, {Company.Name} ON ROWS FROM MyCube WHERE Date.Month.[Feb 2006]

How to get the Payment for the whole 2006, or 2007, or maybe 2006 and 2007?

Regards,

|||

The first query I sent should always work. I think the second query I sent would also work, however this would rely on your attribute relationships being setup correctly and you would need to use a user hierarchy in your where clause that had both the year and month in it. For example, if you had a "Calendar" hierarchy that had the Year and Month, something like the following should work. I don't think that explicitly using the Month attribute will correctly set the Year.

eg

WITH
MEMBER Measures.PrevYrPayment AS (Date.Year.CurrentMember.Lag(1),Measures.Payment)
MEMBER Measures.PrevYr2Payment AS (Date.Year.CurrentMember.Lag(2),Measures.Payment)
SELECT {Measures.Payment
, Measures.Amount
, Measures.Taxes
,Measures.PrevYrPayment
,Measures.PrevYr2Payment
} ON COLUMNS,
{Company.Name} ON ROWS
FROM MyCube
WHERE Date.Calendar.[Feb 2006]

An "older" (but still valid) approach to the calculated member would be to use something like:

WITH MEMBER Measures.PrevYrPayment AS (Ancestor(Date.Calendar.CurrentMember,Date.Calendar.Year).Lag(1),Measures.Payment)

but I am pretty sure that this is redundant in SSAS 2005 as the attribute relationships will take care of this. Sorry, but I can't double check this at the moment as I just got a new laptop and am in the process of re-installing everything.

Cheers
Darren