Sage 100

Excel Pivot Table Sort Order issue Am working on a joint project with John Shaver. I have some of their data on MAS 4.30 exporting to SQL 2008. They use Excel 2007 with a pivot table that use to pull data from another SQL app which is no longer in the picture. I changed the connection string in Excel to pull the data from my table. All the correct data is retrieved, all is good except for 1 thing. There is a field called ScheduledStartDate and it is displayed in columns on the pivot table like this: Col C = 3/18/2013 Col D = 3/25/2013 Col E = 4/1/2013 Col F = 12/17/2012 Col G = 12/24/2012 Notice how the 2012 dates appear at the end despite the sort order being the normal A - Z. In Excel 2007 or 2010 if you were to just key in those dates in ordinary non-pivot table cells, and you go to Data / Sort leave it as A-Z, click OK, you then get an appropriate warning (from attached file). If you leave it at default choice of ""sort numbers stored as text separately"" you get the exact sort problem above. If you choose ""sort anything that looks like a number as a number"" it works perfectly. Unfortunately, when the pivot table sorts dates it doesn't show the sort warning and defaults to the unoptimal choice. I've played with all the pivot table sort options, tried formatting the dates as General, as Date but can't get it to behave. Any ideas?

Did you try grouping them by year? Just a thought !

Alnoor, can you send me the file so I can look at it?

Can you save the original tab as a tab delimited text file, then reopen it in Excel? This will clean the formatting ""junk"" and all behaves normally. Hopefully, this isn't a repetative tesk that you are doing.....

@DianeRuth - I tried that earlier it didn't work. And when it refreshes the data from SQL it reverts back any formatting changes I made. @JeffSchwenk - It didn't work unfortunately and this is a repetitive task. Bottom line is the source data is being treated as general text and the unoptimal sort option is being used. I'll send you the file later. Looks like the source data in SQL needs to be rearranged with the Year first although I'm not sure this client will go for that. They insist it always worked properly when it was pulling data from the old app tables (which I'll have to inspect now). In thinking about this with all the pivot tables you've done @JeffSchwenk the dates in MAS in BF are all stored as YYYYMMDD maybe that's why you've never run into any of this yourself? And even if you were to pull say P/R data into pivot tables the ODBC driver does translate the legacy style compacted dates as YYMMDD

@AlnoorCassim this might be a stupid question but did you create a sep field for Year on the SQL table(Just FYI I don't know SQL)or on the original excel tab and include it on the pivot table - then group it? Then when you refresh it - it should be ok... unless I am not understanding what you are saying and if so sorry and disregard..

Instead of pulling the data directly into the Pivot Table, can you pull your data into a query on one worksheet with an adjacent column formula (which will automatically expand and collapse with the size of the record set returned) that converts the field to a true date and then base your pivot table colum on that? It would still be automatic, but you would have control on the date column outside of SQL server.

Alnoor - try using the format([schedulestartdate], ""yyyy/mm/dd""). I just ran into this today on a cross-tab in access and switching the date format to year first fixed the issue. You've probably recognized that the sort is month order mm/dd in the pivot.

Thank you everyone for all the great ideas and answers. You guys rock. @DianeRuth I didn't group the pivot table rows 1st by year b/c it would not have been acceptable for this client (don't ask). I'm certain that would've worked. @MoiraGoggin I didn't try your idea either cuz below idea was already in progress. Instead I had @Bertowud dial in with me on Friday. His approach was very much like @DanBurleson idea. He created a brand new Excel file. In one sheet/tab he created a table that got the raw SQL data. In another sheet he created the pivot table in a separate sheet to grab the Excel data from the first sheet. This solved the date sort problem. Thank you @RobertWood ! I then had some inconsistent problems auto-refreshing the pivot table data even though I checked the option to Refresh when the file is opened, on both the pivot table Options and the Data / Connections. In the end I told client to manually Refresh All which always works.

I'm glad that worked out for you!