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

Date and text fields in 'Measures' Area

Hi,

I have a fact table like this:

Account No. CreatedDate CreatedBy Amount

1 1/1/2007 serene 1000

2 2/2/2007 mike 2000

.

.

What I want to see in my Cube Browser is an Account on the row side and CreatedDate, CreatedBy and Amount on the 'Measures' Area. Is it possible?

cherriesh

You would not want to setup the CreatedDate and CreatedBy fields as measures, these fields are not aggregateable and really only relate to the Account dimension. What you would want to do is to set them up as attributes in the Account dimension. You could disable them if you did not want explicit analysis done on these fields and only wanted them accessed as member properties.

You might also be able to setup the CreatedDate as a referenced dimension, if you wanted to analyze the creation dates at different levels (like years/quarters/months etc) - if you already have a Date dimension you might be able to reuse it.

|||

Hi,

how does the first scenario work? i should make my fact table as dimension also? how to disable the attribute? thanks a lot for the help!

cherriesh

|||

I'm assuming that you would have some sort of Account dimension table with fields like

AccountId

AccountNo

CreatedBy

CreatedDate

And then a fact table with the dimension keys and the measures

AccountId

DateId

Amount

I'm not sure what your "Amount" is relating to in this example, but if it was a transaction at a point in time then you would not want to be storing the repeated CreatedBy and CreatedDate fields each time a transaction occurred.|||

It's like this...

I have a header table, say, ServiceJob, with the following structure:

ServiceJob ReceivedDate CollectedDate

1 1/1/2007 1/3/2007

2 2/2/3007 2/3/2007

And I have a Service Job Item table:

ServiceJob ServiceJobLineNumber Item Labor Cost Others Cost

1 1 A 1000 200

2 B 700 50

2 1 A 300 900

I want to have this look in my Cube Browser:

Service Job | ReceivedDate CollectedDate LaborCost Others Cost

1 | 1/1/2007 1/3/2007 1700 250

2 | 2/2/3007 2/3/2007 300 900

Service Job is on row side while received date, collected date labor cost and others cost are in the measure side.

can i do that?

cherriesh

|||

You can have multiple attributes on the row axis, so your result set would look like the following, with 3 attributes in the row axis and 2 measures.

Service Job ReceivedDate CollectedDate | LaborCost Others Cost

1 1/1/2007 1/3/2007 | 1700 250

2 2/2/3007 2/3/2007 | 300 900

ReceivedDate and CollectedDate should probably either be dimensions in their own right or attributes of the ServiceJob. Dates don't really "work" well as measures as you cannot aggregate them.