Sorting Dates in a PivotTable Report
Issue: You use Excel as an OLAP browser connected to an SSAS cube. You’ve defined the name of the Month attribute in the Date hierarchy in the format MMM-YYYY. While months are sorted in an ascending order in Excel, descending sorting doesn’t work; or rather Excel sorts them as strings. That’s because Excel interprets these captions as strings instead of dates even if you set the Value property of the Month attribute to a field of DateTime type.
Workaround: You can use the Excel custom sorting feature as a workaround while waiting for Excel to become a better OLAP citizen:
- Connect to the cube and select the Month attribute to get all months displayed in Excel. Select all month cells.
- Click the Excel Office Ribbon button.
- In the Popular tab, click the Edit Custom Lists button.
- In the Custom Lists dialog box that follows, click Import button to create a new custom list for the dates in the format MMM-YYYY. Click OK
As you can see, there is a custom sorting list for months in the format MMM, so descending sort will work if the month captions are in MMM format. However, the end user won’t be able to tell the months apart if the user requests only the Month attribute on the report. So, you kind of have to pick up your own poison.
