MDX to consolidate dates with measures by columns

I have a cube in SQL Server Analysis Services 2005 that has a structure like:

Measures:
Number of Tickets ([Measures].[Number of Tickets])

Dimensions:
Submitted Date ([Submit Date].[Date].[Date])
Submitted By ([Submit By].[Name].[Name])
Assigned Date ([Assigned Date].[Date].[Date])
Assigned To ([Assign To].[Name].[Name])
Resolved Date ([Resolved Date].[Date].[Date])
Resolved By ([Resolved By].[Name].[Name])
....
I would like to have returned a result set that cosolidates the dates with the number of tickets for each (or similarly, consolidates the people with the number of tickets for each). I need a set of all dates back, with the number of tickets aliased appropriately, so that I end up with something similar to the following:

Date # Tickets Submitted # Tickets Assigned # Tickets Resolved
April 1, 2007 14 27 11

April 2, 2007 11 4 null

April 3, 2007 null null 5
... etc.

What is the MDX to do this? I know how I would do it in t-sql, but unfortunately I'm not very proficient in mdx yet. I have tried using LinkMember and can get a single set of dates, but still don't know how to get the different ticket counts for each.

You might be able to figure something out with LinkMember, but it could get messy.

Have you considered changing your structure and setting up a couple of common dimensions instead? This should make a lot of different analysis a lot easier.

Measures:

SubmittedCount, AssignedCount, ResovledCount

Dimensions:

Date

Person

Or even something like

Measures:

ActivityCount

Dimensions:

Date

Person

Activity (Submitted, Resolved, Assigned)

|||

Hi Patty,

as Darren said one possibility is to change the structure of your measuregroup. If you are not able to do so you could try the following (SLOWER!!!) MDX.

If you use the same Date Dimension for all your Dates (-> Same Keys) you can use Filter or VBA to calculate your measures. I tried this in Adventureworks and for me the VBA was faster.

Here The MDX I used in Adventure Works DW:

Code Snippet

WITH MEMBER Measures.[Orders Delivered]

--AS ([Measures].[Order Count], ROOT([Date]), FILTER([Delivery Date].[Date].[Date].MEMBERS, [Delivery Date].[Date].MEMBER_KEY = [Date].[Date].CURRENTMEMBER.MEMBER_KEY).Item(0))

AS ([Measures].[Order Count], ROOT([Date]), StrToMember(VBA!CSTR("[Delivery Date].[Date].&[" + [Date].[Date].CURRENTMEMBER.MEMBER_KEY +"]")))

SELECT

{[Measures].[Order Count],Measures.[Orders Delivered] } ON COLUMNS,

{[Date].[Date].[Date].MEMBERS} ON ROWS

FROM [Adventure Works]

As u see I use the Key Of the Current Date to generate the UNIQUE_NAME in the other date dimension with vba. You should be able to replace Date with Submitted Date and Delivery Date with Assigned Date.

Hope this helps

Markus