When you use the Sort and Top 10 command, the data in a pivot table may not be sorted correctly in Excel 2003 and in Excel 2002

In Microsoft Office Excel 2003 or in Microsoft Excel 2002, you use an Online Analytical Processing (OLAP) source to create a pivot table. When you click the Sort and Top 10 command on the Pivot Table menu to sort the data, the data may not be sorted correctly.

This behavior occurs if the pivot table contains a field that does not have a grand total. For example, this behavior occurs if the pivot table contains a field that specifies a time value such as the year and the month. If the All levels option for a dimension is not included in the OLAP cube, the field that represents the dimension in the pivot table does not have a grand total.

To work around this behavior, use one of the following methods:

• Method 1
  Drag the field that does not have a grand total from the row fields area to the area that is labeled Drop Page Fields Here.
• Method 2
  Perform the following procedure:
  1. Click the field that you want to sort in the pivot table, and then click Sort on the Data menu.
  2. Click Yes when you receive the following message:
     
     This is an AutoSort field.
     
     To sort manually now, and set the AutoSort options on the PivotTable Field Advanced Options dialog box to manual, click Yes. To display this dialog box for a field, double-click the field button and then click Advanced.
  3. In the Sort dialog box, type or select the range of values that you want to sort in the Sort by box, and then click Values under Sort.
  4. Click Ascending if you want to sort the data in ascending order. Or, click Descending if you want to sort the data in descending order.
  5. Confirm that the details that are displayed under Summary are correct, and then click OK.
  Note After you perform this method, data is not sorted automatically every time the pivot table is updated.
• Method 3
  Add the All levels option to the dimension in the OLAP cube. When you do this, Excel summarizes the members of the dimension. Therefore, the field that represents the dimension in the pivot table will have a grand total.