Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Missing PivotItems
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Missing PivotItems
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Missing PivotItems
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 " wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
PIvotitems visible | Excel Programming | |||
PivotItems Bug?! | Excel Programming | |||
Adding PivotItems | Excel Programming | |||
Always keep the same 4 PivotItems visible | Excel Programming | |||
Add PivotItems to PivotTable / Enabling - Disabling PivotItems | Excel Programming |