Sort by key in Excel connected to SSAS OLAP cube

6
votes

I searched the Internet and StackOverflow and was not able to find an answer to this issue. I created an OLAP cube via SSAS with a date dimension. One of the attributes of the dimension is Activity Calendar Month. The NameColumn value is formatted like "January 2015", "February 2015", etc.

The KeyColumns is set as a DATE value type for the first of the month, for each month. Example, NameColumn value "January 2015" = KeyColumns value "1/1/2015", "February 2015" = "2/1/2015", etc.

The ValueColumn is also set to first of the month date (i.e. '1/1/2015') in case Excel was using that for sorting.

When I view the Calendar Month dimension in an Excel 2013 workbook, it initially loads with the correct sorting. Below is a sample:

January 2015
February 2015
March 2015
April 2015
May 2015
June 2015
July 2015
August 2015
September 2015

However, I want to be able to right-click on the field in Excel and select Sort > Sort Z to A and sort the months descending. When I currently do this, I get back:

September 2015
May 2015
March 2015
June 2015
July 2015
January 2015
February 2015
August 2015
April 2015

To me, it appears the Excel will only sort based on the alphanumeric value set in the dimension's NameColumn. Also, if I drop the years from the month values (i.e. "January" instead of "January 2015"), Excel seems to understand that it is a month and sorts the months correctly.

Screen Shot of Dimension Settings

Activity Calendar Month properties

Questions

  • Does Excel have the capability to sort on values outside of NameColumn, such as KeyColumns or ValueColumn, where the NameColumn does not use standard month names (i.e. "January", "February", etc.)?

  • If yes, then what might I be missing in the SSAS dimension setup to make this work?

1
excelssasolap
Did check this? You should order Calendar Month by Month Number Of Year – alejandro zuleta
@alejandrozuleta, yes, I did see that link and tried that. I have tried to set the separate attribute member as a integer (i.e. 1-12) and a date (1/1/2015) for each month. They do sort fine in the Browser tab of the dimension in Visual Studio, and again, are initially sorted correctly in Excel. I still am unable to change the sort order in Excel as it sorts the months alphanumerically by the name, not respecting the sort setting for the dimension. – Shayne Ephraim
@ShayneEphraim, did you get a way to sort in excel based on dimension key field and not name field, as it usually does? – Niraj

1 Answers

3
votes

Can you read the last paragraph here: https://olappivottableextend.codeplex.com/wikipage?title=Show%20Property%20as%20Caption&referringTitle=Home

One advantage of the built-in "Show Properties In Report" feature is that the member properties come through into Excel with their proper datatypes. So a member property of datetime datatype comes across as such and can be formatted using Excel formatting. And numeric member properties come across as such and can be formatted in Excel. However, when you use any member property as a caption using the "Show Property as Caption" OLAP PivotTable Extensions feature, the caption comes across as a string and cannot be formatted in Excel, unfortunately.

It talks about formatting in Excel but I believe sorting in Excel applies too.

So... Add a new member property of your Activity Calendar Month attribute with the same date key (first of the month) then add that member property to the PivotTable and try to sort by it. If you can, great. If not then install that OLAP PivotTable Extensions add-in and use Show Property As Caption then sort. Make sense?

P.S. Thanks for the very well written question!