Months appear in alphabetical order in Excel 2003 pivot table

I have a SSAS 2005 cube attached to a pivot table in Excel 2003. When the months are dragged to the column area, they appear in alphabetical order (Apr, Aug, etc) instead of Jan, Feb, Mar.

The same cube displays the months properly in Visual Studio.

I know I can re-arrange the columns manual but that sort of ruins the point of OLAP if you have to do that every time.

Any pointers would be appreciated.

Make sure you have set the OrderBy property to Key (assuming you have a numeric key column for the month) and set the Type property to Months on the attribute and Time on the dimension. Setting the Type properties will also enable date range filters in Excel 2007 on an attribute of type Date.|||

Thanks for the response.

Unfortunately, all of the properties were already set as you ask.

One thing I am unclear on is the meaning of the term "key" in the OrderBy property. How do I know if the attribute has a numeric key?

Also, this is Excel 2003, not 2007.

|||

The answer is to create another attribute containing the values 1 - 12 (representing the various months) and set the Month variable OrderBy property to AttributeKey and OrderByAttribute to contain the name of the new attribute (you will have to relate the new attribute to the month attribute by expanding the month attribute and dragging the new attribute to <new attribute relationship> under the month attribute.

Works like a charm but I don't see why you should have to do it when Month has already be defined as a time type of attribute.

|||

john shahan wrote:

Works like a charm but I don't see why you should have to do it when Month has already be defined as a time type of attribute.

It sounds like you are using the string of the month name as the attribute key, as such SSAS is just sorting them alphabetically. If you had set your key column to be an actual date or a number it would have sorted using that. (the name and key don't have to be the same column). SSAS works with multiple languages so it will not automatically true to parse month names and sort them in a date order.