How to to combine multiple date dimensions - LinkMember?
MDX Newbie here.
The facts in our cube are based around events, and an event can have these dates (among others) associated with it: LoggedDate, LastInvoiceRejectedDate, InvoiceClearedDate. The cube includes measures for CountInvoicesCleared and CountInvoicesRejected based on their related cleared/rejected dates. For each month, I'd like to view the percentage of invoice actions that are rejections ie
Sum(events with LastInvoiceRejectedDate) x 100 / ( Sum(events with LastInvoiceRejectedDate) + Sum(events with LastInvoiceClearedDate) )
My current query ties each item to the LoggedDate but invoices for an event logged in January could be rejected in Feb then cleared in March. How do I get the monthly counts to correlate in the correct monthly columns (eg an invoice cleared in March should count for March, irrespective of the LoggedDate)? Reading around it looks like LinkMember is a likely contender, but I don't see how to get it to work.
Current query:
WITH
SET [KeyMonths] AS
'[LoggedDate].[All].[2009].children'
-- THESE GENERATE a "Syntax error in axis definition" ERROR
--
-- ('LinkMember([LoggedDate].[All].[2009],[InvoiceClearedDate].[All])).children'
-- ('LinkMember([LoggedDate].[All].[2009],[InvoiceRejectedDate].[All])).children'
SET [ThisYear] AS '[LoggedDate].[2009].[January]:[LoggedDate].[2009].[December]'
SET [Performance] AS
'{
([Measures].[Count Invoices Cleared]),
([Measures].[Count Invoices Rejected])
}'
SELECT
[KeyMonths] ON 0,
[Performance] ON ROWS
FROM [MainCube]
Using Analysis Services 2000.
Cheers
Colin
Have You Considered Role-Playing Dimension for Dates?
I assume you're working with Analysis Services 2k5 or above. Have You considered role-playing dimension treatment of the dates you mention?
Let me know if you have questions ...
Bill
