I have a crosstab that uses a date field to generate columns (Mmm YYYY). I want to sort from left to right chronological order (Jan, Feb, Mar,…), however it defaults to alpha-numeric (Feb, Jan, Mar,…). Is there a way to accomplish what I want?
I just remembered how to force columns by month (PIVOT DateFormat.FsubDate In ("Jan","Feb","Mar","Apr","May","Jun"...). The problem is that I want to include the year and don't want to have to hardcode (..."Dec 2004, Jan 2005"...). Any ideas?
I had a similiar problem (which I also posted here without any help). I did not want to have to adjust my forced column headings each time the fiscal quarter changed and I was also using a Q1 - 2004, Q2 - 2005 column format. The pivot was printing them out "Q1 - 2003 | Q1 - 2004 " etc, instead of "Q1 - 2003 | Q2 - 2003" etc. In other words - printing them out in alpha order.
Since i essentially wanted to sort by year instead of alpha, I simply went to the source of the pivot and added a hidden column that sorted ascending by year.
As long as don't specify column headers in the pivot, the order should be preserved.