2
votes

Am using the MDX query in the SSAS server.

I am trying to sort the Top 201 rows of Business Type in ASC Order By using the below query but the sorting is not applied.

SELECT{
        [Measures].[Reseller Order Count]} ON COLUMNS ,NONEMPTY((TOPCOUNT(
        (Order((([Reseller].[Business Type].[Business Type].ALLMEMBERS)), [Measures].[Reseller Order Count] ,DESC)),201,[Measures].[Reseller Order Count])),{
        [Measures].[Reseller Order Count]}) ON ROWS FROM [Adventure Works] CELL PROPERTIES VALUE, FORMATTED_VALUE, FORMAT_STRING 

enter image description here

Also, when removing the measures column [Reseller Order Count] from TOPCOUNT and forming the query as below sorting is applied, But the measure column [Reseller Order Count] is necessary to be added in TOPCOUNT in my case to achieve the needed result.

SELECT{ [Measures].[Reseller Order Count]} ON COLUMNS ,NONEMPTY((TOPCOUNT( (Order((([Reseller].[Business Type].[Business Type].ALLMEMBERS)), [Measures].[Reseller Order Count] ,ASC)),201)),{ [Measures].[Reseller Order Count]}) ON ROWS FROM [Adventure Works] CELL PROPERTIES VALUE, FORMATTED_VALUE, FORMAT_STRING

enter image description here

So anyone please suggest some solution to sort the [Business Type] without affecting the TOP COUNT function.

Thanks in advance.