Sorting numeric cube dimensions in Excel

3
votes

I have a pretty simple analysis services cube 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.

1
excelolapssas
Thanks, didn't know I needed to do that. – Jeffrey Bane
In general, people are less likely to help when it is very low (such as 0%). – FrustratedWithFormsDesigner
Did you ever find an answer to this? I'm wondering the same thing and haven't found a way to force Excel to sort it numerically, even though it's set as in INT in the cube. – SqlRyan

1 Answers

4
votes

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

enter image description here

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

enter image description here