B
bp
I am wondering if anyone else is having a similar problem or concern and can
provide
some documentation or insight into this problem. The problem occurs in
Excel 2003 as
the program seems to act differently under the same conditions than it does
in earlier
versions of Excel (97, 2000 and 2002). The problem is best illustrated by
using two
pivot tables, one based on the other, as in my example below.
I have a pivot table with three items inserted into the row section of the
layout and
one item in the data section which is the subtotal of the line item. Let's
say the three
items are the Region, City and Store and the subtotal is called Line Item.
Any similar
set of data with three categories and one numeric counter or subtotal should
suffice.
-------
The first pivot table:
The Region and City fields have field settings of Subtotals - None, and on
the
Advanced tab AutoSort and AutoShow are set to Manual.
The Store field has field settings of Subtotals - None, and on the
Advanced tab
AutoSort set to Descending using field Sum of Line Item, AutoShow set to
Automatic,
Show Top 10 using field Sum of Line Item.
This displays the Region and City fields in the order they are found in
the original
data set, with the top 10 Stores in descending order of the Line Items
subtotals.
-------
The second pivot table:
Setup the same as the first table except for the addition of another field
called
ItemDescription in the row section of the layout.
The Region and City fields have field settings of Subtotals - None, and on
the
Advanced tab AutoSort and AutoShow are set to Manual.
The Store field has field settings of Subtotals - None, and on the
Advanced tab
AutoSort set to Descending using field Sum of Line Item, AutoShow set to
Automatic,
Show Top 10 using field Sum of Line Item.
The ItemDescription field has field settings of Subtotals - None, and on
the
Advanced tab AutoSort set to Descending using field Sum of Line Item,
AutoShow set to
Automatic, Show Top ## using field Sum of Line Item (where ## is any number
other than 1).
This displays the Region and City fields in the order they are found in
the original
data set, with the top 10 Stores in descending order of the Line Items
subtotals as in
the first pivot table. In addition it displays the ItemDescriptions listed
in
descending order of the Line Items with the applicable Line Item subtotals
for the
ItemDescription category.
All of this seems straightforward up to this point. Now you will notice
that I
stated in the second table that we chose Show Top ## where ## is any number
other than
1. Here's the interesting situation. If you are in a version of Excel
prior to Excel
2003 you will be able to set this option to Show Top 1 using field Sum of
Line Item
and the items will stay in the order that they appear in the first pivot
table.
However, if you do this in Excel 2003, the order changes to the descending
order of
the ItemDescription item subtotals.
While this may seem intuitive that it is now working the way it probably
should've
in the first place in Excel 2003, it is annoying to those who've become used
to it
working in a particular fashion in earlier versions. I know I can mimic the
behavior
with the addition of an additional field and use it for the appropriate
sorting
operations, however, I'd like another solution thru the interface.
What I'm trying to find out is if anyone know has witnessed this or had a
similar
problem or concern? Also, if anyone knows how to recreate the activity of
the old
versions thru the interface in Excel 2003, or any other means other than
what I've
already stated as I have someone who's been used to doing it that way for
years now?
Any assistance will be appreciated.
provide
some documentation or insight into this problem. The problem occurs in
Excel 2003 as
the program seems to act differently under the same conditions than it does
in earlier
versions of Excel (97, 2000 and 2002). The problem is best illustrated by
using two
pivot tables, one based on the other, as in my example below.
I have a pivot table with three items inserted into the row section of the
layout and
one item in the data section which is the subtotal of the line item. Let's
say the three
items are the Region, City and Store and the subtotal is called Line Item.
Any similar
set of data with three categories and one numeric counter or subtotal should
suffice.
-------
The first pivot table:
The Region and City fields have field settings of Subtotals - None, and on
the
Advanced tab AutoSort and AutoShow are set to Manual.
The Store field has field settings of Subtotals - None, and on the
Advanced tab
AutoSort set to Descending using field Sum of Line Item, AutoShow set to
Automatic,
Show Top 10 using field Sum of Line Item.
This displays the Region and City fields in the order they are found in
the original
data set, with the top 10 Stores in descending order of the Line Items
subtotals.
-------
The second pivot table:
Setup the same as the first table except for the addition of another field
called
ItemDescription in the row section of the layout.
The Region and City fields have field settings of Subtotals - None, and on
the
Advanced tab AutoSort and AutoShow are set to Manual.
The Store field has field settings of Subtotals - None, and on the
Advanced tab
AutoSort set to Descending using field Sum of Line Item, AutoShow set to
Automatic,
Show Top 10 using field Sum of Line Item.
The ItemDescription field has field settings of Subtotals - None, and on
the
Advanced tab AutoSort set to Descending using field Sum of Line Item,
AutoShow set to
Automatic, Show Top ## using field Sum of Line Item (where ## is any number
other than 1).
This displays the Region and City fields in the order they are found in
the original
data set, with the top 10 Stores in descending order of the Line Items
subtotals as in
the first pivot table. In addition it displays the ItemDescriptions listed
in
descending order of the Line Items with the applicable Line Item subtotals
for the
ItemDescription category.
All of this seems straightforward up to this point. Now you will notice
that I
stated in the second table that we chose Show Top ## where ## is any number
other than
1. Here's the interesting situation. If you are in a version of Excel
prior to Excel
2003 you will be able to set this option to Show Top 1 using field Sum of
Line Item
and the items will stay in the order that they appear in the first pivot
table.
However, if you do this in Excel 2003, the order changes to the descending
order of
the ItemDescription item subtotals.
While this may seem intuitive that it is now working the way it probably
should've
in the first place in Excel 2003, it is annoying to those who've become used
to it
working in a particular fashion in earlier versions. I know I can mimic the
behavior
with the addition of an additional field and use it for the appropriate
sorting
operations, however, I'd like another solution thru the interface.
What I'm trying to find out is if anyone know has witnessed this or had a
similar
problem or concern? Also, if anyone knows how to recreate the activity of
the old
versions thru the interface in Excel 2003, or any other means other than
what I've
already stated as I have someone who's been used to doing it that way for
years now?
Any assistance will be appreciated.