Design of 'age' ( dimension ? )

my reports are run over a period of time of certain dates, let s say :

Period 1) DateStartPeriod1 - DateEndPeridod1

Period 2) DateStartPeriod1 - DateEndPeridod1

Period n ) .....

The subject is born in DBORN, so when i need to rollup to the age at the end of the period 1 it is going to be

DateEndPeriod1 - DBORN and the same when the second date is used, i.e. DateEndPeridod1 - DBORN.

QUESTION : How do i model this in Analasys services ? In other words how do i explain AS that when i use Period 1

on the columns i want the age DateEndPeriod1 - DBORN on the rows

I tried to use calculated memebrs AgePeriod1 and AgePeriod2 ... something like

MEMBER AgendPeriod1 as 'DateEndPeriod1 - DBORN'

but it does not seem to work.

I m pretty sure that the answer is straightforward but because i m new to OLAP i just can t think of it.

Thanks

Lui

One possible solution: define a calculated measure.

Formula could be the following:

'[Period].CurrentMember.MemberValue - [Subject].[LastLevel].CurrentMember.Properties("DBORN")'

When designing your cube, make sure ValueColumn for the [Period] hierarchy points to a relational column that has 'date' type, and actually contains the date for the end of each period.

I am also assuming you will have a hierarchy named [Subject], and each member at the last level of this hierarchy has a member property called DBORN, that is also of type date.