Missing PivotItems
My initial guess was that the name of the pivot item had accidentally been
changed or deleted. I noticed that you code is printing both "name" and
"source name" to the immediates window. Is the "source name coming up null
or "Yes". That might give you a clue about the problem being in the query or
in the pivot table. Can you do a simple get external data query and view
what is coming from your source?
It is hard to diagnose this without hands on access.
" wrote:
Hi All:
Im having a problem with a €śmissing€ť PIvotItem that should exist in a
Page Field in a Pivot Table report. The source data is SQL Server and
when I reverse pivot the data I see 3861 records and each record has
a €śYes€ť or €śNo€ť Value in the €śHas_SE€ť field. Thus, there should be
two PivotItems (Yes and No) in the €śHas_SE€ť PivotField, but the €śYes€ť
PivotItem is missing from the selection and the PageField Item choices
look like:
- (All)
-
- No
More Info: The workbook contains multiple pivot tables and they all
have the same problem. The pivot tables are hidden and Im controlling
the pivot table(s) through a combobox that contains the PivotItems
listed above whose items feed the main report using a
€śGetPivotData€ťfunction. Consequently the code fails (as it should)
when it attempts to set the pf.CurrentPage to the missing item €śYes€ť.
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
For Each pf In pt.PageFields
If pf.Name = "Has_SE" Or pf.Name = "SE Assigned" Or
pf.Name = "SE assigned" Then
pf.CurrentPage = Sheet1.ComboBox1.Value
End If
Next
Next
Next
In addition, the following code shows that the ptm.Name is null, but
the record counts are correct.
Sub PrintPtmInfo()
Dim pt As PivotTable
Dim pf As PivotField
Dim ptm As PivotItem
Set pt = Sheet7.PivotTables("BudgetPivot")
For Each pf In pt.PageFields
For Each ptm In pf.PivotItems
If ptm.RecordCount 0 Then
Debug.Print pf.Name & "; " & ptm.Name & "; " &
ptm.RecordCount & "; " & ptm.SourceName
End If
Next ptm
Next pf
End Sub
My question is why does the €śYes€ť item not appear as a PivotItem, but
the €śNo€ť PivotItem does. Thanks for any help!
Zach
|