Ok...been arguiong with this for a while now.
Using Excel 2000, SP3. Have a Pivot Table on some data in an OLAP database (MS Analysis Server specifically)
Here's the grief:
Let's say you have 3 dimensions, DimA, DimB, and DimC, and a single Measure, ItemCount. ItemCount is (of course) in the Data Area, DimA is a page field, DimB is the Row Field, DimC is the Column Field. Everything's fine. I have totals for each member of DimB and DimC as I'd expect, and a Grand Total which is accurate.
HOWEVER...
Expend DimB, and unselect say...5 of the 10 values in that Dimension. As expected, the unselected rows are now no longer visible, but the Grand Totals STILL REFLECT THEIR VALUES.
Since the POINT of a pivot table is, essentially, to see what removing/adding certain values does to the overall picture, this seems to me a ludicrous oversight.
***EDIT***
Worth noting that the behaviour IS NOT present if the source of the data is another Excel worksheet...i.e. if the source data is a list of values on say, Sheet2, then the Grand Total accurately reflects only the displayed rows and/or columns. However, given the amount of data I have in this case, that's simply not an option.
***EDIT***
Anyone have a nifty way to change this behaviour??
Thanks in advance...
Bookmarks