Sort an MDX cross join

Question

I'm new to MDX so bear with me.

I have crossed joined two dimensions so that I can report on Sales & Target (from the sales dimension) with this week and this week last year (from the date dimension) in columns and listed locations in the rows.

Psuedo code..

Select 
    {[DateDim].[ReportingPeriod].[Week].&[2012]&[20],
     [DateDim].[ReportingPeriod].[Week].&[2013]&[20]}
    *
    {[Measures].[Total Sales],[Measures].[Target]} on Columns,

    DrillDownLevel([LocationDim].[LocationName].[All]) on Rows from [MyCube]

How do I sort the results by sales this year? (.. [2013].[20])

Thanks

CResults CResults · Accepted Answer · 2012-10-24T18:09:38

Ah ha, worked it out!

The answer is to calculate the figure separately and then reference it within an order function

With Member SalesTY as
    SUM([Measures].[Total Sales,[DateDim].[ReportingPeriod].[Week].&[2013]&[20])


Select 
    {[DateDim].[ReportingPeriod].[Week].&[2012]&[20],
     [DateDim].[ReportingPeriod].[Week].&[2013]&[20]}
    *
    {[Measures].[Total Sales],[Measures].[Target]} on Columns,

    Order([LocationDim].[LocationName].[LocationName],SalesTY,DESC) on Rows from [MyCube]

Sort an MDX cross join

1 Answers