MDX Query with Total in Cross Dimensions
I have a cube with the following three dimensions: Date, Time and Shift. I have a measure called [Pieces Succeeded] and I want to run the total [Pieces Succeeded] per hour for Shift. The shift can take more than one day, so in the next query I am crossing the date and time sizes.
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 can I get the amount to work on cross connect to work for several days?
thank
+3
source to share
1 answer
I spent all day on this and finally figured it out. I thought this might be of value to someone else, so here is 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]
+2
source to share