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?
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.
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.
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?
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.