Sorting numeric cube dimensions in Excel

I have a pretty simple analysis services 开发者_如何学Pythoncube with a numeric dimension. It works fine in the cube browser, the attribute is set to order by key and it's defined as an integer in the database table. When users connect to it using excel, the dimension initially displays in numeric order. However when the users try to reverse the sort, it now treats the numbers as strings and I'm getting an incorrect sort order, 92, 900, 87, 803, 79, 783, etc. Can excel not sort a numeric dimension from a cube?

Thanks in advance.

Excel appears to treat the values as text when using the options "Sort A to Z" and "Sort Z to A". In order to sort the values like you've defined in the cube, you have to select "More Sort Options..." for the field...

...that first option will sort the values by the KEY (since that's what you explicitly set in the AS database).

However, if you want users to be able to sort in reverse order in Excel, then you're going to have to get creative. One option would be to add a calculated column to this dimension in the DSV based on the value of the original column that you want to sort in reverse. The calculation would be something like...

Ex. [Attribute_RSORT] = 999999 - [Attribute]

...where 999999 is larger than that largest INT value you have in [Attribute]. Add this new attribute to your dimension and set the OrderBy to key. Then you can setup your pivot table like so...

Rows: [Attribute_RSORT], [Attribute] Values: [Measure1]