Let say my cube has 2 dimensions: date-time and location. Data is
stored in local time but some reports are required in Eastern time. The
location dimension has the property 'time shift' and could be from -1
to -3. So I have to pull data with a time shift but display it in EST
time like this

Location1 Location2
Location3 Location4
11/17/2005 00:00 am X Y
11/17/2005 1:00 am XXX YYYY
===============
11/17/2005 23:00 pm XXX

How to do it? Thanks
Anna

Depends on the design of the [Date-Time] dimension. Assuming that there
is an [Hour] level member selected, then applying soemthing like
[Date-Time].Lead(-Val([Location].Properties("time shift")) would give
you the corresponding member in Eastern Time. If this adjustment is
needed for many measures, then a separate utility dimension could be
used to apply it across measures.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Deepak,
Thanks a lot. I feel half way through. My time dimension is
[Year].[Month].[Day].[Hour]
Is it possible (and how if it's) to create calculated members that
display Eastern time and use the existing time dimension with shift to
pull data. Thanks
Anna

Deepak,
Thanks a lot. I feel half way through. My time dimension is
[Year].[Month].[Day].[Hour]
Is it possible (and how if it's) to create calculated members that
display Eastern time and use the existing time dimension with shift to
pull data. Thanks
Anna

Hi Anna,

Taking the simpler case of adjusting a single measure, say
[Measures].[LocalMeasure], to Eastern Time, a new calculated measure
(works only at Hour level) could be defined like:

'([Measures].[LocalMeasure],
[Time].Lead(Val([Location].Properties("time shift"))))'

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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