CODEWITHSUNDEEP
mdx
Nicholas Humphrey
Nicholas Humphrey

Reputation: 1250

MDX order doesn't work

I'm using the AdventureWorks Cube and MS SQL 2017 to learn MDX. I'd like to return the top five selling subcategories of products, based on Internet Sales Amount:

SELECT Subset

(Order

    ([Product].[Product Categories].[SubCategory].members

    ,[Measures].[Internet Sales Amount]

    ,DESC

    )

    ,0

    ,5

)
ON 0
FROM [Adventure Works]

The returned results are not sorted properly. Is there anything I did wrong?

Road Bikes Mountain Bikes Touring Bikes Tires and Tubes Helmets

$29,358,206.96 $26,492,684.38 $10,451,490.22 $925.21 $258,712.93

I also tried BDESC, seems to get the same result.

Upvotes: 0

Views: 275

Answers (2)

GregGalloway
GregGalloway

Reputation: 11625

This query will help explain what happened. It was sorting correctly, but then it was displaying the default measure on the screen since you didn't specify the measure to display, only the measure to sort by:

SELECT {[Measures].[Internet Sales Amount], [Measures].DefaultMember} on 0,
Subset
(Order
    ([Product].[Product Categories].[SubCategory].members
    ,[Measures].[Internet Sales Amount]
    ,DESC
    )
    ,0
    ,5
)
ON 1
FROM [Adventure Works]

I would recommend using that query but removing [Measures].DefaultMember and you should get the results you expect.

Upvotes: 3

The Dumb Radish
The Dumb Radish

Reputation: 896

You want the TOPCOUNT MDX function

SELECT
[Measures].[Internet Sales Amount] ON COLUMNS
,   TOPCOUNT([Product].[Subcategory].[Subcategory].MEMBERS,5,[Measures].[Internet Sales Amount]) ON ROWS
FROM
[Adventure Works]

Upvotes: 1

Related Questions