I have a cube with a Time dimension where non-leaf data is visible.

So an extract from the dimension would be

2004
--2004 data
--Jan
--Feb
--etc
2005
--2005 data
--Jan
--Feb
--Mar

This interferes with the lag function. So [2005].[Mar].Lag(3) returns
[2005].[2005 data] when I was hoping for [2004].[Dec].

Is there any way round this?

Can you use ParallelPeriod instead?

--
_______________
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.

"Dave Morrow" <DaveMorrow (AT) discussions (DOT) microsoft.com> wrote

Hi Dave and Richard,

Based on this thread from the newsgroup, there might be a problem with
ParallelPeriod(), in that it didn't seem to ignore Data Members. Chris
Webb had a work-around:

http://groups-beta.google.com/group/...rver.olap/msg/
649d96e9f56e2f0d
From: "Chris Webb"
Date: Fri, 7 Jan 2005 05:27:03 -0800
Subject: Re: ParallelPeriod and Cousin functions don't work with
Parent-Chi

You will have a problem with hidden members with data - see the other
fork of
this thread. Parallelperiod doesn't seem to be able to ignore the hidden
members, so you'll need to write some MDX which does the same job as
Parallelperiod. Here's an example from Foodmart 2000 of what it might
look
like (unfortunately it's not very nice, but I've tried to make my
example as
flexible as Parallelperiod; you should be able to simplify it depending
on
your real requirements):

With
/* As Deepak pointed out, this returns the wrong members for Jessica
Olguin,
Jonathan Murrain and Concepcion Lozada */
Member Measures.PrevEmplWrong as 'ParallelPeriod([Employees].[S*enior
Management], 1, [Employees].CurrentMember).Nam*e'
/* so instead, here's some MDX which does return the right members */
Member Measures.PrevEmplRight as 'IIF(Count(
{Ancestor(
[Employees].CurrentMember,
/*enter level argument that you would otherwise use in ParallelPeriod
here */
[Employees].[Senior Management]).Lag(/*enter distance here*/1)} as
myset)=0,
"",
Subset(
Descendants(
myset
,[Employees].CurrentMember.Lev*el)
,Rank([Employees].CurrentMembe*r,
[Employees].CurrentMember.Sibl*ings)-1,1
).Item(0).Item(0).Name)'
Select {Measures.PrevEmplWrong,Measur*es.PrevEmplRight} on 0,
[Employees].[Level].Members on 1 From HR

HTH,

Chris

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Dave,

How about just using .lag(4) to skip over the aggegate [2005 data]?

Jim

"Dave Morrow" wrote:

In fact, I am doing something similar to that, but it is messy as the current
period and lag number are dynamic. I simplified the scenario to explain the
problem clearly. I am using MDX queries to display data in Reporting Services
- obviously I am not really hard coding the current period and the lag number
required, it depends on user selections.

"Jim_OLAP" wrote: