LAG MDX statement

Will Riley

I have an MDX statement to return a prior period result

([Dim Time].[FullDateAlternateKey].CurrentMember.Lag(1), [Measures].[EOD Book Balance])

which successfully returns the previous day's balances for a selected date

My question is this - is it possible to add something to / or replace the LAG number so that this value is somewhat dynamic. I was thinking that i could do this

([Dim Time].[FullDateAlternateKey].CurrentMember.Lag([Dim Time].[DayNumberofYear]), [Measures].[EOD Book Balance])

So as to return the Year "Opening Balance". But that didn't work - can you do this Or am I barking up the wrong function

Deepak Puri

Lag() takes a numeric argument, so if the attribute: [Dim Time].[DayNumberofYear] has an integer member value defined, you could use: .Lag([Dim Time].[DayNumberofYear].MemberValue). For example, the Adventure Works DayOfYear attribute has an integer value:

>>

With

Member [Measures].[BegOfYear] as

[Date].[Calendar].Lag(

[Date].[Day of Year].MemberValue - 1).MemberValue

select

{[Measures].[BegOfYear]} on 0

from [Adventure Works]

where [Date].[Calendar].[February 1, 2002]

-----------------------------------------------------------------------------

BegOfYear
1/1/2002

>>

Will Riley

Cheers, I'll try that & get back to you.

I had tried .CurrentMember with no success.... MemberValue hadn't occurred to me (!)

Will Riley

OK, we're really getting somewhere - I used this

With

Member [Measures].[BegOfYear] as

([Time].[Day of Year].Lag(

[Time].[Day of Year].MemberValue - 1),[Measures].[EOD Book Balance])

select

{[Measures].[BegOfYear],[Measures].[EOD Book Balance]} on 0

from [DailyBalances]

where [Time].[Day of Year].&[23]

Which gives me the correct result, however I'd really want to pass a date parm to the WHERE clause - unfortunately this produces an error... do you know why

Deepak Puri

Well, it depends on what the MDX looks like with the parameter, and what value is being passed in. Are you using Reporting Services - if so, what's the data type of the parameter

Will Riley

Well, I guess what i'd envisage is the ability for the user to select a date, i.e. Jan 31 2007 (this would be a string format as per the "friendly date" style of construct on MSDN here

So i'd end up with something like

With

Member [Measures].[BegOfYear] as

([Time].[Day of Year].Lag(

[Time].[Day of Year].MemberValue - 1),[Measures].[EOD Book Balance])

select

{[Measures].[BegOfYear],[Measures].[EOD Book Balance]} on 0

from [DailyBalances]

where [Time].[SImple Date.&[Jan 1, 2007]

This though would I guess throw an error, so is there anyway to pass the relevant "Day of Year" to the MDX even if the user selects my "friendly date" from a dropdown

Deepak Puri

This really is another topic in itself; but if the the "user-friendly" string is the name of the member, then the ampersand isn't needed:

With

Member [Measures].[BegOfYear] as

([Time].[Day of Year].Lag(

[Time].[Day of Year].MemberValue - 1),[Measures].[EOD Book Balance])

select

{[Measures].[BegOfYear],[Measures].[EOD Book Balance]} on 0

from [DailyBalances]

where [Time].[Simple Date].[Jan 1, 2007]