Sorting PowerPivot Labels Manually
Posted by denglishbi on June 23, 2010
One of the items that you are going to run into is the sorting order of items in PowerPivot. For the most part you might get by with items being in ascending or descending order, but there are going to be times when a more manual approach might be needed. One particular case would be the order to calendar month labels in a report. Initially it would be displayed in alphabetical order like the following:
So how can you get this in the order that you are used to seeing the months displayed? As you can see this is not how you want it and it isn’t like Analysis Services where you can define an additional column to sort the data by. One option would be to have an additional sort order column that you could concatenate with the month label like the posting here by the PowerPivot Team – Sorting month by natural sort order. Another option is to use the manual sorting functionality that is available. How does that work? It isn’t extremely obvious and after a bit of playing around I finally figured it out. Here is how you can do it:
1. Right-click the label and select Sort and then More Sort Options…
2. In the Sort dialog box select the Manual (you can drag items to rearrange them) option and then click OK
3. Now in the PivotTable select an item and place the cursor towards the right-hand side of the cell until you get the arrows displaying in all four directions
4. Now click on that cell and drag it to the desired spot where you would like it to be displayed
5. Then drop the item and now you have not only moved the item with this display, but everywhere it is being displayed in the PivotTable
6. Now simply repeat the process until everything is in the proper order that you want for reporting. Note: this is just for this PivotTable.
There you go. Just another option that you have for sorting items in your PowerPivot reports.