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?
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.
Dr. Dallas Snider is an Assistant Professor in the Computer Science Department at the University of West Florida. He received his Ph.D. in Integrated Computing and M.S. in Instrumental Sciences from the University of Arkansas at Little Rock. He received a B.A. in Physics from Hendrix College. Before joining UWF, he worked as a data warehouse developer for Northrop Grumman Information Systems and prior to that as a database application developer for Acxiom and Euronet. Dr. Snider’s teaching and research interests include data mining, data warehousing, information visualization, and software development.