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