November 20, 2009 at 4:44 am
I have Schools and each school has ‘Number of session codes’ For each term (1 through to 6) Each term has the Max date against it. Currently the MDX is
SELECT NON EMPTY { [Measures].[PCT ATT - FACT Sessional Attendance By School],
[Measures].[TOT Al INV PUPILS],
[Measures].[TOTAL RCVD],
[Measures].[PCT AUTH UNAUTH - FACT Sessional Attendance By School], [Measures].[PCT UNAUTH - FACT Sessional Attendance By School], [Measures].[PCT AUTH - FACT Sessional Attendance By School], [Measures].[CURRENT ATT SUB], [Measures].[CURRENT MAIN ATT] }
ON COLUMNS, NON EMPTY
{ ([Dim Date Time SA].[AC YEAR].[AC YEAR].ALLMEMBERS * [Sessional School].[SCH DFEE].[SCH DFEE].ALLMEMBERS * [Dim Date Time SA].[Date].[Date].ALLMEMBERS * [Sessional School].[SCH NAME].[SCH NAME].ALLMEMBERS * [Sessional School].[PHASE DESC].[PHASE DESC].ALLMEMBERS ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( { [Dim Date Time SA].[AC YEAR].&[AC 09-10] } ) ON COLUMNS FROM [CAYA Data Warehouse Dev SA])
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
And it looks like for example (2 rows)
[AC YEAR], ‘AC 09-10’
[SCH DFEE],’1234’
[DATE], ‘2009/10/23’
[SCH_NAME], ‘Test School’
[PHASE DESC], ‘Secondary’
[CURRENT MAIN ATT],4
[CURRENT ATT SUB], 5
[TOTAL RCVD], 2
[TOT Al INV PUPILS],15
[PCT ATT], 91.43
[PCT AUTH],5.71
[PCT UNAUTH], 2.86
[PCT AUTH UNAUTH]8.57
[AC YEAR], ‘AC 09-10’
[SCH DFEE],’1234’
[DATE], ‘2009/10/23’
[SCH_NAME], ‘Test School’
[PHASE DESC], ‘Secondary’
[CURRENT MAIN ATT],4
[CURRENT ATT SUB], 5
[TOTAL RCVD], 2
[TOT Al INV PUPILS],13
[PCT ATT], 90
[PCT AUTH],10
[PCT UNAUTH], 0
[PCT AUTH UNAUTH]10
As you can see here there are 2 rows for the same school. This is because currently there are 2 terms of data.
How to I do this so that I just get the max date and then….
The attendance for that max date
The sub attendance for that max date
The sum of total invalid pupils
The total received for that specific max date
For percentage it would be Attended / Possible sessions for everything.
I think that in reality I may have to create a new fact table for just the year which would then be a lot easier to manage and then the other fact table would have the data by term.
Does any one have any ideas about what the better option would be?
Thanks in advance
Debbie
March 12, 2010 at 4:07 am
i created another fact table 🙂
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply