January 13, 2011 at 8:50 am
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
January 13, 2011 at 9:17 am
The Key is only used in Analysis services, when you sit any reporting tool on top of an SSAS cube the key becomes irrelevant.
As a result Excel knows nothing of the Key so as far as Excel is concerned the Dimension Attributes are all strings, thus you get this kind of behaviour.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
January 13, 2011 at 10:11 am
Wow, thanks for the info, but that's a terrible limitation. Is there no OLAP client that will treat numbers as numbers in dimensions? Or anything I can do in Excel to get it to see the numbers as numbers?
January 14, 2011 at 1:44 am
It is a bit of a limitation, unfortunately I'm not aware of anything out thier that will behave any differently.
You might be able to use the MDX Order() function to sort the Set, but that would probably mean creating a custom Set to accomodate it, even then I'm not sure it will work as expected. Something like
ORDER({[dim].[Hierarchy].children}, [dim].[Hierarchy].currentmember.properties("key"),desc)
You may have to play around a little with the MDX as I'm not sure what the correct term for the properties is.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply