Greetings Everyone,

This would be easiest to explain if you take a moment to look at the
StudentMigration Sample.JPG attachment.

Scenario: I have an analytical application that must show Student
Migration from one grade to the next. For example, assume I have 3
students over 2 years. StudentA advances from Grade 1 to Grade 2,
StudentB is put back from Grade 4 to grade 3, and StudentC remains in
Grade 5 for both years. I need to be able to display:

Advanced : 1
Same: 1
Regress: 1

Now, assume I have a different 3 students: StudentA advances from
Grade 1 to Grade _3_, StudentB is put back from Grade 4 to grade 3,
and StudentC is put back from grade 3 to grade 2. I need to be able to
display:

Advanced : 1
Same:
Regress: 2

So the measure I am trying to create is not simply the totals at each
grade, but the sum of members who were in the same grade, advanced a
grade, or declined a grade, when considering current period against
prior period.

If anyone is curious about this and wants to play with the data, the
second attachment contains all the files you might possibly need:
StudentMigration.csv - my fact table data
StudentMigration.SQL - SQL Script to set up the very simple test
data, including an insert script
Student Migration.CAB - AS2K archive
StudentMigration Sample.JPG
StudentMigration.MDX - MDX that gets kind of close to what I want
to do, but not all the way
StudentMigration_Data.MDF - the detached SQL 2000 database

The fact table is just a simple flat file to test this scenario. It
contains the following columns:
StudentMigration_PK - Integer Primary Key (not strictly needed for
this example)
StudentName - First name
StudentYear - Year portion of date
StudentGrade - Member Name of Grade
StudentGradeKey - Member key to indicate order of Grade members.

I have a feeling there's an elegant way to solve this using MDX, but I
must be thinking about this the wrong way. I hope there's someone here
who's had their vitamins today and feels up to the challenge.

Regards,

Tom

p.s. you can contact me via newsgroups or at
tomhug[atsign][cee][dee][double-u].com
-- or --
SELECT
char(116)+char(111)+char(109)+char(104)+char(117)+ char(103)+char(64)+char(99)+char(100)+char(119)+ch ar(46)+char(99)+char(111)+char(109)+char(32)
AS MyEmail