Using the MDX lag and lead functions

Question

I'm attempting to query an SSAS cube using MDX, I have a list of numeric values which are ordered in the cube and I want to refer to an adjacent cell in the cube in order to return the value.

WITH MEMBER Measures.[LastShift]<br>
AS<br>
(<br>
    [Measures].[Impact Value GBP], <br>
    [Dim Shift].[Shift Value1].PrevMember<br>
)<br>
MEMBER Measures.[NextShift]<br>
AS<br>
(<br>
    [Measures].[Impact Value GBP], <br>
    [Dim Shift].[Shift Value1].NextMember<br>
)<br>
SELECT <br>
    {NONEMPTY([Measures].[Impact Value GBP]), <br>
    Measures.LastShift,<br>
    Measures.NextShift<br>
    }<br>
    ON 0,<br>
    NONEMPTY([Dim Shift].[Shift Value1].[Shift Value1])<br>
    ON 1<br>
FROM [factTradeValues]<br>

I can see in the RDBMS that I do have adjacent values, but I cannot get the MDX to return anything other than null. Maybe I'm being thick, but cannot see the solution.

Thanks for any help

Answer 1

PrevMember and NextMember are "member functions" so you need do something like this...

WITH 
  MEMBER Measures.[LastShift] AS 
    (
      [Dim Shift].[Shift Value1].CurrentMember.PrevMember,
      [Measures].[Impact Value GBP]
    ) 
  MEMBER Measures.[NextShift] AS 
    (
      [Dim Shift].[Shift Value1].CurrentMember.NextMember,
      [Measures].[Impact Value GBP]
    )

Answer 2

To answer my own question there turned out to be issues with prevmember and nextmember, because I wanted to refer to the next item in turns of the context of my criteria. I actually wanted to refer to the next item in my set, which is subtly different.

In order to refer to an item in a set you use item and then I used rank to get the adjacent cell.

/* Define the shifts appropriate for this scenario */
SET [SpotShifts] AS
EXCEPT(
NONEMPTY([Dim Shift].[Shift Value1].[Shift Value1].MEMBERS,
(
    [Dim Shift Entity].[Shift Entity].[GBP/FTSE],
    [Dim Date].[Date].[Date].&[20120305],
    {PriceImpacts
    }
    ,[Dim Instrument].[Arena Name].[CO/GBP/Lch10/FTSE/140908/Inc_Quarter/5L]
)),[Dim Shift].[Shift Value1].&[0])

MEMBER Measures.CurrentSpotShift
AS
StrToValue([Dim Shift].[Shift Value1].CURRENTMEMBER.NAME)

MEMBER Measures.NextSpotShift AS
IIF((Measures.CurrentSpotShift<0),
SpotShifts.Item(RANK([Dim Shift].[Shift Value1].CURRENTMEMBER,SpotShifts)-2).NAME,
SpotShifts.Item(RANK([Dim Shift].[Shift Value1].CURRENTMEMBER,SpotShifts)).NAME)

MEMBER Measures.PreviousSpotShift AS
IIF((Measures.CurrentSpotShift<0),
SpotShifts.Item(RANK([Dim Shift].[Shift Value1].CURRENTMEMBER,SpotShifts)).NAME,
SpotShifts.Item(RANK([Dim Shift].[Shift Value1].CURRENTMEMBER,SpotShifts)-2).NAME)

MEMBER Measures.NextValue AS
IIF(Measures.CurrentSpotShift>0,
([Measures].[Impact Value],SpotShifts.Item(RANK([Dim Shift].[Shift Value1].CURRENTMEMBER,SpotShifts))),
([Measures].[Impact Value],SpotShifts.Item(RANK([Dim Shift].[Shift Value1].CURRENTMEMBER,SpotShifts)-2))

SELECT      {   NONEMPTY([Measures].[Impact Value]),
            (Measures.PreviousSpotShift),
            (Measures.LastValue),
            (Measures.NextSpotShift),
            (Measures.NextValue)} ON 0,
        NONEMPTY(SpotShifts) ON 1
FROM factTradeCube

I hope this helps somebody as I found the use of rank within item as not an obvious solution to this, but the only one that got me the correct results.