ssas - MDX query to ORDER a Measure based on Multiple dimensions

Question

I have a simple mdx query shown below. I want to order [Measures].[NumberOfCustomers] based on multiple dimensions on the ROW Axis.

SELECT
NON EMPTY
{ [Measures].[TotalCost]
  [Measures].[Damages]
  [Measures].[SpecialDamages]
  [Measures].[NumberOfCustomers]
} ON COLUMNS,
NON EMPTY
{
  ([OpenDate].[FullDate].[FullDate].ALLMEMBERS *
  [CloseDate].[FullDate].[FullDate].ALLMEMBERS *
  [DimCustomer].[Customer Group].[Customer Group].ALLMEMBERS *
  [DimType].[Type].[Type].ALLMEMBERS)
} ON ROWS
FROM Cube

I want to order [Measures].[NumberOfCustomers] to return below

*******************************************************************************************************************
|OpenFullDate   |ClosedFullDate |Customer Group |Type   |TotalCost  |Damages    |SpecialDamages |NumberofCustomers|
|*****************************************************************************************************************|
|2020-01-20     |2020-03-30     |Retail         |Active |￡2,000     |￡400       |￡200           |10               |
|2020-02-10     |2020-03-10     |Commercial     |Active |￡10,000    |￡500       |￡100           |8                |
|2020-01-02     |2020-04-30     |Manufacturing  |Active |￡2,000     |￡40        |￡100           |6                |
|2020-03-20     |2020-10-30     |Servicing      |Active |￡5,000     |￡350       |￡300           |5                |
|2020-01-20     |2020-04-30     |Law            |Active |￡1000      |￡100       |￡50            |3                |
|*****************************************************************************************************************|

I use the mdx query below

SELECT 
NON EMPTY 
{ [Measures].[TotalCost]
  [Measures].[Damages]
  [Measures].[SpecialDamages]
  [Measures].[NumberOfCustomers]
} ON COLUMNS,
NON EMPTY 
ORDER (
         { ([OpenDate].[FullDate].[FullDate].ALLMEMBERS *
          [CloseDate].[FullDate].[FullDate].ALLMEMBERS *
          [DimCustomer].[Customer Group].[Customer Group].ALLMEMBERS *
          [DimType].[Type].[Type].ALLMEMBERS)}
          ,[Measures].[NumberOfCustomers],BDESC ) ON ROWS
FROM [Cube];

I get the following error message

Any help will be appreciated.

question from:https://stackoverflow.com/questions/65862404/mdx-query-to-order-a-measure-based-on-multiple-dimensions

Answer 1

Waitting for answers