Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Change Sort Options on Dimension Attributes in SQL Server Analysis Services


By:   |   Read Comments (4)   |   Related Tips: > Analysis Services Dimensions

Attend these FREE SQL Server 2017 webcasts >> click to register


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


Last Update:


signup button

next tip button



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.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Saturday, August 08, 2015 - 6:26:55 AM - Aashish Back To Top

Very Nice article.

Very Helpful topic

Thank a lot.


Sunday, July 26, 2015 - 4:29:25 PM - Michael Back To Top

Thank you so much! You helped me a lot.


Tuesday, May 05, 2015 - 9:06:33 AM - Arshad Hamid Back To Top

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

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.


Learn more about SQL Server tools