Missing PivotItems

[zacharybass]

Hi All:

I’m 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 I’m 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

 

[TomPl]

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.

 

[zacharybass]

Thanks for the input. I still had the same problem after refreshing
the data so I rebuilt the pivot table which seemed to solve the
problem. To answer your question, the ptm.Name was null, but the
ptm.SourceName was populated. I spent a lot of time trying to figure
it out, but in hindsight it was best to rebuild and move on.

Zach

 

[TomPl]

Now I am convinced that the field was renamed somehow because the sourcename
was correct. One solution might have been to rename it back to what it
should have been (the source name).

I'm glad you have a solution.

Tom