A tale of 2 activities

I’m trying to calculate the difference between turn times. However, the employees performing each activity aren’t the same, and I want to make sure that the employee performing the starting activity gets credit for the turn time. Below is a copy of my MDX, the problem like I say is that I don’t know how to specifically assign the credit for the turn time. I’ve also attached a copy of the SQL version of what I’m trying to do in MDX.

Non-working MDX code

SELECT

NON EMPTY{

{([Measures].[Activity Turn Time]//This is an int and in seconds

,[Activity Code].[Activity Code].&[TD])}//TD is the ending activity

-

{([Measures].[Activity Turn Time]//This is an int and in seconds

,[Activity Code].[Activity Code].&[AP])}//AP is the starting activity

} ON COLUMNS

,{([Employee].[Emp Full Name].ALLMEMBERS)}ON ROWS

FROM[Employee Scorecard]

Working SQL code

SELECT AP.Emp_Full_Name

,SUM(TD.Activity_Turn_Time - AP.Activity_Turn_Time) as TD_to_AP

FROM TD

JOIN AP ON

TD.Application_ID = AP.Application_ID

GROUP BY AP.Emp_Full_Name

How about trying a calculated measure, like:

With Member [Measures].[TD_to_AP] as

([Measures].[Activity Turn Time]//This is an int and in seconds

,[Activity Code].[Activity Code].&[TD])//TD is the ending activity

-

([Measures].[Activity Turn Time]//This is an int and in seconds

,[Activity Code].[Activity Code].&)//AP is the starting activity

SELECT

NON EMPTY{

[Measures].[TD_to_AP]

} ON COLUMNS

, [Employee].[Emp Full Name].[Emp Full Name].MEMBERS ON ROWS

FROM [Employee Scorecard]

|||

I guess I’m not being clear. My MDX code executes and returns results. The problem is that sometimes the person performing the AP and the TD activity are different people and I want to specifically assign the results to the person who does the ending activity (TD).

|||

Your original MDX query might not execute exactly as you expect - note that:

{([Measures].[Activity Turn Time]//This is an int and in seconds

,[Activity Code].[Activity Code].&[TD])}//TD is the ending activity

-

{([Measures].[Activity Turn Time]//This is an int and in seconds

,[Activity Code].[Activity Code].&)}//AP is the starting activity

}

is equivalent to this first set alone because, with the '-' operator, you're trying to remove a non-existent tuple from the first set:

{([Measures].[Activity Turn Time]//This is an int and in seconds

,[Activity Code].[Activity Code].&[TD])}//TD is the ending activity

Anyway, to replicate the SQL query logic, there should be something like an [Application] dimension with an [Application_ID] attribute:

Code Snippet

With Member [Measures].[TD_to_AP] as
Sum(NonEmpty([Application].[Application_ID].[Application_ID],
{([Activity Code].[Activity Code].&[AP], [Measures].[Activity Turn Time])}),
([Measures].[Activity Turn Time]//This is an int and in seconds,

,[Employee].[Emp Full Name].[All] // since ending employee may be different
,[Activity Code].[Activity Code].&[TD])//TD is the ending activity
-
([Measures].[Activity Turn Time]//This is an int and in seconds
,[Activity Code].[Activity Code].&[AP])//AP is the starting activity
)

SELECT
NON EMPTY{
[Measures].[TD_to_AP]
} ON COLUMNS
, [Employee].[Emp Full Name].[Emp Full Name].MEMBERS ON ROWS
FROM [Employee Scorecard]

|||

This is really close to what I need. However, for some reason when I do this the TD numbers are way higher than expected.

Code Snippet

With Member [Measures].[TD_to_AP] as

SUM(

NonEmpty([Fact Application].[Fact Application].[All]

,{([Activity Code].[Activity Code].&[TD]

,[Special Activity First].[Special Activity First Name].&[First Activity]

,[Measures].[Activity Turn Time])}

)

,([Measures].[Activity Turn Time]//This is an int and in seconds,

,[Employee].[Emp Full Name].[All] // since ending employee may be different

,[Special Activity First].[Special Activity First Name].&[First Activity]

,[Activity Code].[Activity Code].&[TD])//TD is the ending activity

-

([Measures].[Activity Turn Time]//This is an int and in seconds

,[Special Activity First].[Special Activity First Name].&[First Activity]

,[Activity Code].[Activity Code].&[AP])//AP is the starting activity

)

SELECT

NON EMPTY{[Measures].[TD_to_AP]} ON COLUMNS

,{[Employee].[Emp Full Name].[Emp Full Name].ALLMEMBERS}ON ROWS

FROM [Employee Scorecard

]

|||

The 1st parameter of NonEmpty() should be the set of individual members, not the [All] member, ie. instead of:

NonEmpty([Fact Application].[Fact Application].[All] // this member aggregates all application records

it should be something like:

NonEmpty([Fact Application].[Fact Application].[Fact Application] // the set of individual application records to filter