CODEWITHSUNDEEP
ssasmdxcube
Vincent
Vincent

Reputation: 88

MDX query result All element should show last tuple not a sum

I would like to have a different value on the All element of the mdx query result. This value should be the value of the last tuple from a specified dimension attribute. For example:

Student     Schoolyear      Grade
Fred        All             B
Fred        2009 / 2010     A
Fred        2010 / 2011     A
Fred        2011 / 2012     B
Wilma       All             C
Wilma       2009 / 2010     B
Wilma       2010 / 2011     C

So the value on the All element should be the last element in the second column, in this case "Schoolyear", but could be any dimension attribute. For "Wilma" it wil be the Grade in "2010 / 2011" and for "Fred" the grade in "2011 / 2012".

I tried to limit the measure on the All member to the lastChild but this wont work. In the example below I have a working query but it's not dynamic because "Schoolyear" "2010 / 2011" is not dynamic but hardcoded. 

MEMBER [Measures].[GradeFixed] AS 
Iif( [Dimension School].[Schoolyear].Currentmember IS 
[Dimension School].[Schoolyear].[All], 
[Dimension School].[Schoolyear].[2011 / 2012]
,
[Measures].[Grade] )

Has anyone an idea to limit this measure dynamicly to the last element in a dimension for the All element?

Upvotes: 1

Views: 1013

Answers (1)

Jeroen Bolle
Jeroen Bolle

Reputation: 1836

Scope ( 
    [Dimension School].[Schoolyear].[All],
    [Measures].[Grade]
);
This = Tail (
    NonEmpty (
        [Dimension School].[Schoolyear].[Schoolyear],
        [Measures].[Grade]
    ), 1
).item(0)

Upvotes: 1

Related Questions