Change Sort Options on Dimension Attributes in SQL Server Analysis Services

By:   |   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.

We will demonstrate an easy way to change the displayed sort order for a dimension attribute.

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

Open the Date dimension in Visual Studio

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

scroll down to the Source grouping and click on the ellipsis

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

Remove the EnglishMonthName column from the Key Columns box

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

Click on MonthNumberOfYear in the Available Columns box

Click on the ellipsis to the right of the NameColumn.

Click on the ellipsis to the right of the NameColumn.

Click on EnglishMonthName and click on OK.

Click on EnglishMonthName and click on OK.

Click on the ellipsis to the right of the ValueColumn.

Click on the ellipsis to the right of the ValueColumn.

Click on MonthNumberOfYear and click on OK.

Click on MonthNumberOfYear and click on OK.

The changes are now complete.

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.

Deploying and processing the changes to the Analysis Services server
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Dallas Snider 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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Tuesday, June 19, 2018 - 9:05:13 AM - Harshit Back To Top (76247)

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 Back To Top (38409)

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 Back To Top (37096)

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 Back To Top (30911)

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.















get free sql tips
agree to terms