Change Sort Options on Dimension Attributes in SQL Server Analysis Services

By: Dallas Snider | Comments (5) | Related: > Analysis Services Dimensions

Problem

By default, SQL Server Analysis Services will sort and display dimension attribute values in alphabetical order. When displaying the days of the week or the months of the year (as shown below), we often want to see the data in chronological order instead of alphabetical order. How can we solve this problem?

Changing Sort Options on Dimension Attributes in SQL Server Analysis Services

Solution

In this tip, we will demonstrate an easy way to change the displayed sort order for a dimension attribute.

As shown below, we have a simple cube built from the Fact Sales Quota measures table and the Date and Employee dimensions.

Display the Properties Window, open the Date dimension in Visual Studio and choose the column whose displayed sort order needs to be changed.

In the Properties Window, scroll down to the Source grouping and click on the ellipsis (...) to the right of the KeyColumns.

Remove the EnglishMonthName column from the Key Columns box by pressing the "<" button.

Click on MonthNumberOfYear in the Available Columns box and move it to the KeyColumns by pressing the ">" button. Click on OK.

Click on the ellipsis to the right of the NameColumn.

Click on EnglishMonthName and click on OK.

Click on the ellipsis to the right of the ValueColumn.

Click on MonthNumberOfYear and click on OK.

The changes are now complete.

After deploying and processing the changes to the Analysis Services server, we can browse the cube and see the month names in chronological order.

Next Steps

More complicated sort keys using year and month will need to be built as a column in the data source view. Also, check out these other tips about SQL Server 2012 Analysis Services.

About the author

Dr. Dallas Snider is an Assistant Professor in the Computer Science Department at the University of West Florida and has 18+ years of SQL experience.

View all my tips

Comments For This Article

Tuesday, June 19, 2018 - 9:05:13 AM - Harshit

Hello,

I'm working on some SSAS question using AdventureWorks DataBase. I'm not able to sort data in descending order

How to Retrieve all the Countries in descending order of their Internet sales amount of year 2007, which are not null.

Saturday, August 8, 2015 - 6:26:55 AM - Aashish

Very Nice article.

Very Helpful topic

Thank a lot.

Sunday, July 26, 2015 - 4:29:25 PM - Michael	Back To Top (38292)
Thank you so much! You helped me a lot.

Tuesday, May 5, 2015 - 9:06:33 AM - Arshad Hamid

Thanks Dr. Dallas, you saved me from a lot of extra R&D. I've followed the steps mentioned by you. So far everything is ok, but the only problem I'm facing is that instead of the String Month Names, Month Numbers are displayed in the grid. I've carefully verified that the NameColumn is the String Month Name, but still. Am I missing something?

Appreciate your help.

Regards,

Arshad Hamid

Friday, May 23, 2014 - 5:09:31 PM - ken

Alternatively, you can add an attribute relationship between EnglishMonthName and MonthNumberOfYear and then change the Order By on the EnglishMonthName attribute from Key to AttributeName or AttributeKey (whichever is sorted the way you want it) and then select MonthNumberOfYear as the OrderByAttribute.

This might work out better for situations where you are already grouping the attribute key columns by other values and you don't want to add your sort column to that grouping. I have had to use this method many times when sorting for fiscal months where the first month of the year isn't January but is instead September or November. Sorting by the attribute name of an ID field helped me there.