Sorting members in MDX query based on member values

How to sort members returned in MDX query based on member values. Below are some examples:

SELECT Measures.MEMBERS ON COLUMNS,
ORDER({[Store].[Store City].[Beverly Hills]:[Spokane]}, [Store].CURRENTMEMBER.Name,BASC) ON ROWS FROM [Sales]


SELECT Measures.MEMBERS ON COLUMNS,
ORDER({[Store].[Store City].MEMBERS},Measures.[Sales Count], BDESC) ON ROWS
FROM [Sales]


select {[Measures].[Unit Sales], [Measures].[Store Cost]} ON columns,
crossjoin({[Product].[Product Category].Members},
{ORDER (except([Promotion Media].[Media Type].Members,
{[Promotion Media].[Media Type].[No Media]}),
[Measures].[Unit Sales],DESC)}) on rows
from Sales

Database : FoodMart 2000

1 comment:

Aman Agarwal said...

Thanks :)
Solved my problem :D

My Articles

Design

Cube structure optimization for MDX query performance in Analysis Services 2005 SP2: Tips for Parent Child Hierarchies usage

Fact table design for “State Workflow Analysis”: Analysis Services Dimensional modeling

Handling inter-dimensional members dependency and reducing cube sparsity using reference dimensions in Analysis Services 2005 SP2 : Cube design tip

Identifying intra-dimensional members relationship and reducing cube sparsity in Analysis Services 2005 SP2 : Cube design tip

Leaves() : An example to understand it for both regular hierarchies as well as parent child hierarchies

Aggregation design: useful tips

Level based attribute hierarchy: MDX query performance woes in SQL Server 2005 SP2: Is it fixed in post SP2 hotfix?

Parent child hierarchy to level base hierarchy conversion: hiding placeholder dimension members in client application

Trouble / Troubleshooting

Aggregate(), Sum() functions using calculated members does not work in Analysis Services 2005 SP2 (9.00.3042.00 version) but works in Analysis Services 2000 SP4

Analysis Services 2005 migration tool: Custom member formula issues in migrated database

Cube Partitions: Fact table not listing in Business Intelligence Development Studio in partition wizard

Analysis Services 2005: Many-to-Many relationship does not support unary operators with parent-child dimension

MDX

NextAnalytics and MDX : Part 1 - Swap Cells with Row Labels

Selecting dimension's default member based on a member property

Sorting members on member codes / member properties

Time Dimension: How to set Default Member to Current Month

Setting dynamic default member in dimension X based on the current member of dimensions Y

ADOMD.NET

Code : utility code for converting cellset to a data table

Others

Google specialized search for Analysis Services and MDX web resources integrated in my blog

Art of reading MDX articles

MDX Expression Builder : Need for a tool making it easier for functional users to write MDX expressions, queries.

Blogroll