Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm creating a pivot table by connecting to an SSAS tabular model (Data -> From Other Sources -> From Analysis Services) .

The model has a "time" column that I want to sort by. The default (database) sorting is earliest to latest:

Default sorting

When I click the triangle next to 'Row Labels' and select "Sort A to Z", I get alphabetically sorted times:

enter image description here

How can I get the times to sort by time? Changing the number format from "General" to "Time" does nothing. The times aren't stored as text either - the data type of the column in the SSAS model is Auto (Date)

share|improve this question
How about using a helper column with a UNIX timestamp?… – Oliver Salzburg Oct 17 '13 at 21:24
@OliverSalzburg That would work perfectly for me but I'm trying to find the solution for all the "differently intelligent" business people that are going to be using it. It would involve lots of explaining and they wouldn't like it. – Turch Oct 17 '13 at 21:26
How about changing the formatting to a 24-hour based format? Would that be a sufficient option? I don't know if it's possible without any helper constructs. But I may be mistaken – Oliver Salzburg Oct 17 '13 at 21:28
Also, I assume I must be doing something wrong - is sorting times and dates (which do the same thing) really not a big enough feature to be implemented in excel? – Turch Oct 17 '13 at 21:29
Excel does understand dates and times, but only when they are formatted as such. I see that the data is stored as a date in the DB, but is it coming into Excel as a date? What happens if you try to divide one of the dates by two? (i.e. in Cell C2 type '=A2/2" without the quotes). This will let us know how it is stored in Excel. – guitarthrower Nov 4 '13 at 18:11

You should change the number format of the Row Field to Custom hh:mm:ss AM/PM from Time.

Row Labels > Field Settings > Number Format > Custom > hh:mm:ss AM/PM

This solved your issue for me.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .