MDX Query use most recent value

Hi

I am no MDX Expert, so I need help, say I have a cube with the following data (simplified)

Month EE CoCode Value

jan   A  1      100  
feb   A  1      200  
Mar   A  2      400  
Jan   B  1      150  
Feb   B  1      100  
Jan   C  2      100  
Mar   C  2      650  

Month, EE and CoCode are all separate dimensions

EE A changed cocode in march, so I want to report on his total YTD

Result in report I want is

EE  CoCode  Tota开发者_如何学编程l  
C   2       750  
A   2       700  
B   1       250  

Not sure if solution is in

the MDX(using currentmember or something for CompanyCode),

the cube(create new attribute in CompanyCode Dimennsion for currentCoCode)

or in grouping in the report.

Thanks paul

Actual MDX I am using uses parameters and is a bit more complicated as follows

="SELECT NON EMPTY { [Measures].[ValueCode] } ON COLUMNS,   
topcount(NONEMPTY ([Dim E Eno].[EE No].[EE No].ALLMEMBERS * [Dim E Eno].[Sur Name].[Sur Name].ALLMEMBERS * [Dim E Eno].[First Name].[First Name].ALLMEMBERS * [DIM Cost Centres].[Cost Centre].[Cost Centre].ALLMEMBERS * [DIM Location Codes].[Location Code].[Location Code].ALLMEMBERS * [DIM Grades].[Grade Code].[Grade Code].ALLMEMBERS * [DIM Company Codes].[Company Code].[Company Code].ALLMEMBERS * [Dim Codes].[Description].[Description].ALLMEMBERS * [Dim Codes].[Code].[Code].ALLMEMBERS * [Dim Codes].[Long Description].[Long Description].ALLMEMBERS ),"+Parameters!P_TopN.Value+",[Measures].[ValueCode] ) ON ROWS  
 FROM ( SELECT ( -{ [DIM Cost Centres].[Cost Centre].&[9006890557], [DIM Cost Centres].[Cost Centre].&[NA18350157], [DIM Cost Centres].[Cost Centre].&[NA18312257], [DIM Cost Centres].[Cost Centre].&[WB18350357], [DIM Cost Centres].[Cost Centre].&[EC18350157], [DIM Cost Centres].[Cost Centre].&[HSS8800257] } ) ON COLUMNS  
 FROM ( SELECT ( STRTOSET(@DIMCompanyCodesCompanyCode, CONSTRAINED) ) ON COLUMNS  
 FROM ( SELECT ( STRTOSET(@DimDatesYear, CONSTRAINED) ) ON COLUMNS   
 FROM ( SELECT ( { [Dim Codes].[Code].&["+Parameters!P_Reports.Value+"] } ) ON COLUMNS  
 FROM [DW Datatrieve]))))   
 WHERE ( IIF( STRTOSET(@DimDatesYear, CONSTRAINED).Count = 1, STRTOSET(@DimDatesYear, CONSTRAINED), [Dim Dates].[Year].currentmember ) ) " 

The answer will depend on whether you want to always show the current CoCode as of now or whether you want to run a query for a specific period and show the CoCode "as at" that period.

If you always need the current CoCode regardless of period then make CoCode an attribute of EE as you suggest.

If you want the "as at" then you'll need to make CoCode a slowly changing attribute on EE.

Have a look up Slowly Changing Dimensions (SCD's) and see what you can find.