June 22, 2011 at 7:25 am
Hi.
I have a Date/Calendar dimension in my cube. When I open my cube in SSAS cube browser, and then expend year to quarter and then quarter to month, those months labels show in correct order, Jan, Feb, Mar and so on. But when I do the same in Excel 2007, when I am connected to the same cube, the order of those months is alphabetical. I tried to change different sorting options, like "Data source order", "Ascending by Month", "Autosort" and so on, nothing works. It's like Excel completely ignores it's own settings and only sorts alphabetically.
Is there a way to make it work ?
Please, help. It's an urgent project.
Thank you,
Victor.
June 23, 2011 at 6:11 pm
You need to make sure that attribute of the dimension is sorted by a key. I usually put a NumberOfMonth column in my date/time dimension of 1-12, and you can use that as the key to sort by. Excel really is a PITA with that...
June 23, 2011 at 7:18 pm
Thank you for your reply.
I had it sorted by key. What I didn't have was the KeyColumn collection set up properly. Actually, I had 2 fields in my collection: Month and fiscal year. I thought that was enough to make it a unique key. But it looks like it wasn't enough. I had to add a third field, that was just a record ID, to that collection. And then it worked properly. I am not sure why it wanted that record ID field in the KeyColumn collection, but as long as it works, who cares ? 🙂
June 24, 2011 at 6:49 am
I'm assuming the months are in order in your time dimension, so that record ID basically served as the MonthNo I mentioned. It just provided a number to order by. Glad it worked out!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply