After a pivot table is refreshed, if some pivot item is no long exist in a pivot field, the pivot field drop selection still show that pivot item to allow user to select. Selecting this kind of pivot item will lead to no data showing at all.
This is no good.
I perfer Excel will remove zero record count pivot item from the pivot field drop down selection box after a refresh.
(I don't mind Excel keeping zero record count pivot item internally (this is good to preserve manual sort order of pivot item -- one refresh may return zero record for a pivot item, next refresh may have some records returned), but never show this pivot item in the pivot field drop down select box)
Furthermore, comparing pivot table with data autofilter, data autofilter is smarter.
In data autofilter, when column A has selected certain value and now try to drop select column B's value. Column B's drop down box does not show all original column B distinct values, but it shows all value of in the subset of records already filtered by column A. This is nice.
However, this similar concept is not found in Pivot Table. It often likes this in pivot table, after pivot field A is filter and try to further filter pivot field B, no record may returned.
Will Excel 2003 be improved on these 2 point about pivot table in some patch or next release
Why Excel 2003 keeps PivotItems with zero record count after refresh?
Hot Topic
- What's your experience with the buddy program?
- Disabling enter and mouse click PPT navigation
- handling volume control in C#
- Getting Excel Formulas to work in VBA
- BSM Scorecard Report Views
- Access report using VBA
- Application Error
- VBA HELP ON WORKSHEET
- Microsoft Business Scorecard Security
- Form Requery
Microsoft ISV
- Output property to save as
- Real example: Converting a legacy database to SQL with client/server capability
- Excel, Forms, and Charts
- access - importing specific data from excel
- Excel: Treeview handle events for dynamically added control
- MS Office document and image writer print driver
- .NEt and industrial automation
- VBA runs slowly in Excel 97
- VBA macro from office 2000 excel does not run after downloading current office 2000 upgrades
- Word: Normal.dot template menu bar
Why Excel 2003 keeps PivotItems with zero record count after refresh?
Nick Swan
AGSP
Until Version 12 comes out, here is a VBA routine that will clear the Pivot Table Cache;
Option Explicit
Sub Delete_Unused_PivotFields()
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim ptTable As PivotTable
Dim ptField As PivotField
Dim ptItem As PivotItem
Dim i As Long
Set wbBook = ThisWorkbook
On Error Resume Next
For i = 1 To 2
For Each wsSheet In wbBook.Worksheets
For Each ptTable In wsSheet.PivotTables
For Each ptField In ptTable.PivotFields
For Each ptItem In ptField.PivotItems
ptItem.Delete
Next ptItem
Next ptField
ptTable.RefreshTable
Next ptTable
Next wsSheet
Next i
On Error GoTo 0
End Sub
Good luck,
Bud
Florian Kr&#252&#59;sch
Hi,
Here's some information direct from the Excel experts:
Whether or not items deleted from the data source are persisted, and therefore show up in the PivotTable UI, is controlled by an object model property (PivotCache.MissingItemsLimit).
You can set this property to none (PivotCache.MissingItemsLimit = xlMissingItemsNone) which makes deleted items in the data source be deleted from the PivotTable cache as well and then you will not see these items anywhere in the PivotTable UI or in the PivotTable report (if deleted items exist, they will be shown in the PivotTable report when “Show items with no data” is turned on for the field).
In Excel 12, we have added the PivotCache.MissingItemsLimit property to the PivotTable Options dialog so you can easily control these behaviors from the UI. In previous versions, you had to use the object model to set it.
When deleted items are persisted in the PivotTable cache, we display them in the manual filter UI (drop down selection box) so you can control their filter state.
Also, filtering one field will not affect the items listed in the drop downs for other fields. We are considering options here to make the PivotTable filter experience more similar to the AutoFilter experience in the future but it’s a bit more complicated with PivotTables given all the operations you can perform after filtering and this is not changing in the Excel 12 release.
You can read about new Excel 12 features and enhancements (including PivotTables) in the Excel 12 blog: http://blogs.msdn.com/excel/
-brenda (ISV Buddy Team)
Cortexbomber
Thanks for this, it is helpful, but new items are appended to the bottom of the field item list.
How can I programmatically resort the field items alphabetically or numerically
webdevsam
andreas-fsc
In the pivot table, right-click on the field you want to sort
A small menu appears; click Field Settings ...
The PivotTable Field box appears; click on the Advanced button.
The PivotTable Field Advanced Options box appears; under AutoSort Options click on the Ascending (or Descending) radio button.
Click the OK button twice