MDX - SSAS, getting result count of last measurement within a period

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » SQL Server Newbies » MDX - SSAS, getting result count of last...

MDX - SSAS, getting result count of last measurement within a period

Rate Topic

Display Mode

Topic Options

Author

Message

Jens Henrik

Posted Wednesday, March 06, 2013 1:42 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, March 13, 2013 2:26 AM
Points: 1, Visits: 16

Hi I am having trouble trying to write some MDX code.
I am trying to get the last measurement result pr. person within a period. I have the following measurements and results:

Person      Measurement  result              Date
1           1            overweight         2011-01-01
1           2            Normal             2011-03-01
2           3            Underweight        2011-01-01
2           4            Underweight        2011-02-01
2           5            Normal             2011-03-01
1           6            overweight         2012-01-01

Right now I am only able to get a normal count on measurements and distinct count on persons. So I can create a table like this (Full period):

           Number of persons            Number of measurements
Overweight       1                       1
Normal           2                       3
Underweight      1                       2

But I would like to have only the last category within my chosen period, so it looks like this (Full period):

            Number of persons        Number of measurements
Overweight       1                  1
Normal           1                  1
Underweight      0                  0

But it should also be able to create a table like this for the period (year 2011)

            Number of persons        Number of measurements
Overweight       0                   0
Normal           2                   2
Underweight      0                   0

My dimensions and measurements are like the following:

[Measures].[Numb persons]
[Measures].[Numb measurements]
[Registrationdate].[Date]
[Person].[ID]
[result].[Name]

I hope someone can help me
thanks in advance

Br
Jens H

Post #1427234

« Prev Topic | Next Topic »

Permissions