Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Wrong sorting order of Date dimension in Excel.

Status
Not open for further replies.

victordba

Programmer
Jan 10, 2007
10
US
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.
 
A couple of things you might try:

- Add an additional attribute to the date dimension which is the date formatted as "MM/DD/YYYY". Excel might pick that up as a date.
- Add a additional column(s) as above but for YYYYMMDD or YYYY and MM and DD, or YYYYMM. Even if Excel doesn't recognize the date, it will be sorted in the right order.

The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright
 
Thank you, John.
I already solved this problem. I my Calendar table besides Month Short (Jan, Feb, Mar and so on) I also have a field Month Number (1,2,3 ...). So, in the properties for Month Short I set the KeyColumn as Month Number, and for NameColumn I set Month Short. Works perfectly good.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top