How to sort records in MDX query when numeric expression is used with TopCount function

Question

I am trying to sort the Top 201 records in my SSAS cube either ascending or descending based on requirements in my C# code.

Here I a using Adventure Works Database, where [Reseller Order Count] is the measure and [Reseller].[Business Type] is the dimension.

Please find the details below here.

MDX Query For Ascending Sorting

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])),{
        [Measures].[Reseller Order Count]}) ON ROWS FROM [Adventure Works] CELL PROPERTIES VALUE, FORMATTED_VALUE, FORMAT_STRING

Sorting Result:

In the following result, descending sorting is applied instead of ascending sorting.

While checking the documentation, I have found that by default the Descending sorting will be applied.

Link: https://learn.microsoft.com/en-us/sql/mdx/topcount-mdx?view=sql-server-ver15#remarks

Question:

My question is how to achieve ascending sorting as well as descending when the numeric expression is added in Top Count in MDX query in a generic manner?

@ACV - I have updated the query in my question. Only Descending is applied by default even for ascending sorting MDX query. Thanks — Sathish G, Jan 18 '22 at 13:27

score 0 · Answer 1 · answered Mar 01 '22 at 13:46

Try changing the ASC keyword (to signify ascending order) to BASC instead. The B indicates that this will break the hierarchy of the [Reseller] dimension whose members are being listed.

The phrase "break the hierarchy" means that the ordering of values takes precedence over the ordering of the dimension's hierarchy.

(With ASC the measures are only ordered within items that are grouped together at the same level.)

How to sort records in MDX query when numeric expression is used with TopCount function

1 Answers1