MDX Query with running total across crossjoined dimensions

Question

I have a cube with the following three dimensions: Date, Time and Shift. I have a measure called [Pieces Succeeded], and I want a running total of the [Pieces Succeeded] by hour for a Shift. A Shift can span more than one day, so in the following query, I do a crossjoin of the Date and Time dimensions.

with
member [Measures].[Pieces Succeeded Running Total] as
    sum([Time].[Hierarchy].[Hour].FirstMember:[Time].[Hour].CurrentMember, [Measures].[Pieces Succeeded])
select
    { [Measures].[Pieces Succeeded], [Measures].[Pieces Succeeded Running Total] } on columns,
    nonempty(crossjoin([Date].[Month Hierarchy].[Day].Members, [Time].[Hierarchy].[Hour].Members)) on rows
from
    [OEE]
where
    [Shift].[Month Hierarchy].[Shift].&[501]

Which gives the following results:

Date        Hour  Pieces Succeeded  Pieces Succeeded Running Total
03 Apr 2011 22    6393              6393
03 Apr 2011 23    6424              12817
04 Apr 2011 00    3816              3816
04 Apr 2011 01    5510              9326
04 Apr 2011 02    2090              11416
04 Apr 2011 03    7489              18905
04 Apr 2011 04    7307              26212
04 Apr 2011 05    5706              31918

How would I go about getting the sum to work on the crossjoined set so that the Running Total works across days?

Thanks

Answer 1

I spent all day on this, and finally figured it out. I thought it might be valuable for someone else, so here's the solution:

with

set DateHours as
    nonempty(crossjoin([Date].[Month Hierarchy].[Day].Members, [Time].[Hierarchy].[Hour].Members), [Measures].[Pieces Succeeded])

member [Measures].[Rank] as
    rank(([Date].[Month Hierarchy].CurrentMember, [Time].[Hierarchy].CurrentMember ), DateHours) 

member [Measures].[Running Pieces Succeeded] as 
    iif([Measures].[Rank] = 1, [Measures].[Pieces Succeeded], sum(head(DateHours, [Measures].[rank]), [Measures].[Pieces Succeeded]))

select
    { [Measures].[Pieces Succeeded], [Measures].[Running Pieces Succeeded] } on columns,
    non empty { DateHours } on rows

from
    [OEE]

where
    [Shift].[Month Hierarchy].[Shift].&[501]