Sorting Columns in a Pivot Report in Classic Designer

Sorting columns in a pivot report is a little different than sorting columns in a standard report. When sorting on a column in a pivot report, any columns that are above that column or to the left of that column in the pivot report also need to be sorted. You can also include a column in a pivot report to use solely for sorting purposes and hide the column so that it does not display in the report.

To apply sorting to columns in a pivot table

Build your pivot table in Classic Designer by dragging and dropping columns to the pivot control as in the example below.
For this example, we would also like to show months in the pivot table, so we will add Month to the Column quadrant of the pivot control. This produces the following result:
You can see above that the months are not sorted correctly. This is because Month is sorted alphabetically by default. To solve this problem, add Month of Year as shown below.

After adding Month of Year, we now see the month numbers in the report (the months are still out of order):
Next, sort by Month of Year in order to put the months in the right order. Click Month of Year in the pivot control and select Sort from the menu.
In the Column Sorting dialog box, put the columns in the order in which they should be sorted and select the type of sorting to apply. A higher level column that is being sorted needs to be higher in the list than a lower level column that is being sorted. Drag and drop the columns up and down to put them in the correct sort order. In this example, we are applying sorting (in ascending order) to Year and Month of Year. Note that Year needs to be above Month of Year. If Year is not sorted first, Year will be duplicated for each column.

Note: Measures in pivot tables cannot be sorted.
Sorting by Year and Month of Year produces the following report:
The months are now sorted correctly, but we don't want to display Month of Year in the report. We can choose to not display this column in the Column Properties. Click the name of the column that you don't want to display (Month of Year in this example) and select Column Properties from the menu.
In the Column Properties dialog box, uncheck the Show Column in Report box.

The Show Repeating Values check box only applies to standard reports. It does not have any effect on pivot reports.
The report sorts by Year and Month of Year (so that the months display in the correct order) and Month of Year is hidden.