One of the most used SQL syntax in reporting is Order by, so it is normal to expect a similar functionality in MDX with a simple keyword.
How to order the results based on single column in SQL?
Let us first execute a simple query against AdventureworksDB
SELECT geo.City
,sum(SalesAmount) InternetsalesAmount
FROM dbo.FactInternetSales fis
join DimCustomer cus on fis.CustomerKey = cus.CustomerKey
join dbo.DimGeography geo on cus.GeographyKey = geo.GeographyKey
join dbo.DimDate dt on fis.OrderDateKey = dt.DateKey
where dt.CalendarYear = 2005
group by geo.City
order by City asc
The above query filter the records for 2005 and order the results based on city in ascending order
How to get the similar results in MDX?
In MDX we need to
- Remove the empty (null) values using non empty function
- order the values by
- using the Order function
- with non hierarchy ascending order (basc)
- based on the city names derived using membervalue(used to convert the values to string) function.
- using the Order function
SELECT
{ [Measures].[Internet Sales Amount] } ON COLUMNS,
order(nonempty( [Customer].[Customer Geography].[City]
, [Measures].[Internet Sales Amount]
)
,[Customer].[Customer Geography].currentmember.membervalue
,basc
)
ON ROWS
FROM
[Adventure Works]
WHERE
( [Date].[Calendar].[Calendar Year].&[2005] ) ;
How to order the results based on multiple columns in SQL?
SELECT geo.City,sum(SalesAmount) InternetsalesAmount
FROM dbo.FactInternetSales fis
join DimCustomer cus on fis.CustomerKey = cus.CustomerKey
join dbo.DimGeography geo on cus.GeographyKey = geo.GeographyKey
join dbo.DimDate dt on fis.OrderDateKey = dt.DateKey
where dt.CalendarYear = 2005
group by geo.City
order by 2 asc, City asc
The above query first sort the results by internetsales amount and if we have the same total amount for multiple cities then they get sorted in ascending order.
Note: check the sort order in the order by class, it is internet sales amount followed by city
How to order the results based on multiple columns in MDX?
In MDX we need to
- Remove the empty (null) values using non empty function
- order the values by
- using the Order function
- in non hierarchy ascending order (basc)
- based on the city names derived using the membervalue function.
- using the Order function
- Order the values by
- using the Order function
- with non hierarchy ascending order (basc)
- based on internet sales amount.
- using the Order function
SELECT
{ [Measures].[Internet Sales Amount] } ON COLUMNS,
order (order(nonempty( [Customer].[Customer Geography].[City], [Measures].[Internet Sales Amount])
,[Customer].[Customer Geography].currentmember.membervalue
,basc
)
,
[Measures].[Internet Sales Amount]
,basc
)
ON ROWS
FROM
[Adventure Works]
WHERE
( [Date].[Calendar].[Calendar Year].&[2005] ) ;
Note: check the sort order, it is city followed by internet sales amount
Why should we eliminate the Null values in the MDX query? Won’t that degrade query performance?
SSAS cubes never store the Null values so it is merely a representation. So by including the Nonempty function you are actually increasing the performance of the query
Why we are reversing the order of columns in MDX?
In SQL we are using a single order by keyword which accepts multiple columns. It orders the results by first column and if the values are same then it further order the rows based on second column. So in our example the results are ordered by internet salesamount and when the values are same (e.g 3374.99) it has further been ordered by CIty
In MDX we are using two order by function, so we are ordering the results twice. The query works only because of the stable sort algorithm which is used in MDX order function
E.g. let us use Bubble sort to prove this theory
In the below example I have already sorted the results by City and using the bubble sort to sort the results based on Amount.
Bubble sort is a stable sort and as you can see, whenever amount are equal between multiple rows, it remember and retain the position of pre-Sorted CITY order.
