Sorting data by dimensions

There are usually two types of sorting requirements in reporting: one is to sort by numeric measures and the other is to sort by dimension members alphabetically.

Data from SSAS is naturally sorted by attributes in dimensions. Members in an attribute can be sorted by its own key or name value or sorted by another attribute's key or name value. The two properties that are related to member sorting are OrderBy and OrderByAttribute.

For example, if we put all members from the Date attribute on the ROWS axis, the results will already be sorted in ascending order of the date. This is because the Date attribute is sorted by its key value in the cube.

[Date].[Date].[Date].MEMBERS 

If we CROSSJOIN multiple sets, as shown in the ...

Get MDX with Microsoft SQL Server 2016 Analysis Services Cookbook - Third Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.