Post reply

Creating Age ffrom Dateofbirth and Date from the time dimension at the time of the fact

  • March 12, 2010 at 4:06 am

    #386488

    I have a date time dimension with all the dates in.

    I have a child table with DOB.

    In Reporting Services I would like to take DOB and convert to age based on the date time dimensions. So for example

    DOB09 JAN09 Feb09 March

    01/03/2007100

    03/01/2002001

    23/05/2000010

    This is the results set on Date of Birth

    Would get changed to (Aggregated)

    Age09 JAN09 Feb09 March

    2100

    9001

    10010

    I would assume I could create a calculated measure as DATEDIFF(year, date, DAteofBirth) but its not doing anything.

    I’ve tried created an age dimension but it takes hours to run because I need the age as at every year.

    Can anyone think of a way of doing this?

    Thanks in advance.

    Debbie

  • March 12, 2010 at 8:46 am

    #1132768

    Im thinking it may be using Anaysis Services as a calculated name set but I still need the MDX.

    Ive done a really basic MDX query with the details I think might be involved

    SELECT

    NON EMPTY {[Measures].[Re Registrations On The CPR]} ON COLUMNS,

    NON EMPTY

    {([Dim Date Time].[Last 12 Months Hierarchy].[Date],

    [Child].[Fwi DOB].&[1986-01-01T00:00:00])} ON ROWS

    FROM [Social Care Child Protection Registrations]

    reregistrations on the CPR

    2001-11-30 1986-01-011

    Age as at date reregistrations on the CPR

    2001-11-30 1986-01-01 1

  • March 22, 2010 at 3:03 am

    #1137450

    This is one of the many reasons I like using views on my fact and dimension tables.

    Inserting a view between the tables and the DSV in SSAS allows you to do your datediff calculation, and provide an 'Age' field worked out from the DOB stored in the underlying table.

  • March 22, 2010 at 3:25 am

    #1137457

    Ill look into that!

    Ive managed to make an age dimension that contains the following e.g.

    Pupil ID Date OF Birth Month year Age

    1 11/11/1990 11 1990 0

    1 11/11/1990 11 1991 1

    1 11/11/1990 11 1992 2

    1 11/11/1990 11 1993 3

    1 11/11/1990 11 1994 4

    1 11/11/1990 11 1995 5

    etc

    Hopefully this would link to the date of the fact so you can see what the age is every year.

    Still to be tested though :crazy:

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply