5
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:
When I click the triangle next to 'Row Labels' and select "Sort A to Z", I get alphabetically sorted times:
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)
Turch
Posted 2013-10-17T21:18:25.133
Reputation: 216
1
How about using a helper column with a UNIX timestamp? http://stackoverflow.com/questions/1703505/excel-date-to-unix-timestamp
– Der Hochstapler – 2013-10-17T21:24:46.1631@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 – 2013-10-17T21:26:52.850
1How 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 – Der Hochstapler – 2013-10-17T21:28:49.450
1Also, 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 – 2013-10-17T21:29:16.567
@OliverSalzburg That's a good solution (24 hour time) if my above comment is wrong - is it? – Turch – 2013-10-17T21:30:57.360
Well, I'm not really sure if Excel has "deep" knowledge of the type of data that is in a cell. Also, I might just not know the proper solution. Maybe one can implement a custom search somehow that directly does the required conversion. But I'm just very used to working with helper columns. – Der Hochstapler – 2013-10-17T21:32:15.840
@OliverSalzburg Hmm, I guess this is more of an in-depth question about SSAS and excel for the MSDN forums, rather than just a general excel question. You can add your suggestions as an answer and I'll accept in a few days if no one else chimes in. – Turch – 2013-10-17T21:33:40.250
We have good Excel people here as well. Just give it a little time ;) In case you get a solution elsewhere, it would be appreciated if you would replicate it here. Cheers – Der Hochstapler – 2013-10-17T21:38:55.013
2Excel 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 – 2013-11-04T18:11:02.333