Sorting on a measure within Multiple time calcualations

Ok, can anyone give me a hand with the following.

I have a cube, with time calculations in it.. MTD, YTD etc

I have created a calculated Measure that ranks each customer by the sales amount.

I have a report like this created in Excel as a pivot table..


Fiscal Hierarchy	2007 Q1 P1

	Time Calculations	Data
	YTD		YTD Pr Yr
Customer Group Name	Sales Amount	Customer Rank	Sales Amount	Customer Rank
A.D.I. LIMITED	$10.03	96
AGCO AUSTRALIA LIMITED	$6,453.16	37	$1,373.12	62
AIMS INDUSTRIAL	$3,097.86	47	$2,486.35	52
AIR INTERNATIONAL THERMAL	$721.00	72	$721.00	69
BAT ENGINEERING	$2,996.92	48	$2,969.56	49
BAXTERS	$301.00	81	$10,375.44	29
BURSON AUTOMOTIVE	$612.46	73	$3,162.69	48
BYNORM	$948.40	66	$10,395.92	28
CANTERBURY TRANSPORT SPARES	$21.36	95	$555.60	75
CAR AIR WHOLESALE			$296.90	83
CASH SALES	$78.15	92	$2,741.92	50
CATALOGUES ACCOUNT	$0.00		$0.00
CC COMPONENTS			$10,282.82	30
CENTRIFUGAL AIR PUMPS	$1,038.74	65	$1,674.09	57
CHAMPION COMPRESSORS	$319.90	79	$248.60	85
CHIORINO AUSTRALIA	$5,437.94	40	$11,804.07	27
CHRIS MILLS PERFORMANCE	$780.57	70
CONSOLIDATED BEARING COMPANY	$33333.36	3	$444444	2

I would like to sort the customers based on their rank in the YTD Column., I have tried select to sort the customers using the Customer Rank field, but it doesnt sort it correctly.. I was thinking as each of the time calculation fields have the customer rank under it, that it will not let me sort based on 1 time calculation field. Problem is, i need to show YTD, Pr YTD, MTD, Pr MTD all on the one report.

Is there anyway i can do this.. either in the report or in the MDX statment for Customer Rank.. ive included it below:

CREATE MEMBER CURRENTCUBE.[Customer Rank]

IIF([Measures].[Sales Amount] = 0, NULL,

Rank([Customer].[Customer Group Name].CurrentMember,[Customer].[Customer Group Name].CurrentMember.Level.Members,[Measures].[Sales Amount])),

VISIBLE = 1 ;

Answer this question