Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
PivotItems.Count is 0 when items are visible on the sheet
Hi All
I have a Pivot Table report (Excel 2003 sp1) connecting to a cube on SQL Server 2000 (sp 3a) and am trying to enumerate through a pivot field so that I can print a report for each item in it. On the worksheet there are 20 or so items selectable, but when I try to select one through code, the items count is 0. Below is a fragment of code that I am using (taken from other postings!): ' Dim pt As PivotTable Dim pf As PivotField Dim pi As PivotItem Set pt = ActiveSheet.PivotTables.Item("PivotTable1") ' this is ok as I can do pf.Name Set pf = pt.PivotFields("[Department]") ' this always displays 0 MsgBox pf.PivotItems.Count ' Does anyone have any sugestions please?!?! I have tried ChildItems and also working through CubeFields but by the time I get to PivotItems it all ends!!! Incidently ColumnFields seems ok, but I can't use this because of the way I have to format the report. Many thanks for any help/suggestions iD |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
PivotItems.Count is 0 when items are visible on the sheet
Hi again
Does anybody have even the slightest idea about this please? I can't find anything about it, not even on Microsoft's site. Many thanks iD iD wrote: Hi All I have a Pivot Table report (Excel 2003 sp1) connecting to a cube on SQL Server 2000 (sp 3a) and am trying to enumerate through a pivot field so that I can print a report for each item in it. On the worksheet there are 20 or so items selectable, but when I try to select one through code, the items count is 0. Below is a fragment of code that I am using (taken from other postings!): ' Dim pt As PivotTable Dim pf As PivotField Dim pi As PivotItem Set pt = ActiveSheet.PivotTables.Item("PivotTable1") ' this is ok as I can do pf.Name Set pf = pt.PivotFields("[Department]") ' this always displays 0 MsgBox pf.PivotItems.Count ' Does anyone have any sugestions please?!?! I have tried ChildItems and also working through CubeFields but by the time I get to PivotItems it all ends!!! Incidently ColumnFields seems ok, but I can't use this because of the way I have to format the report. Many thanks for any help/suggestions iD |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
PivotItems.Count is 0 when items are visible on the sheet
Hi iD
to start with, unfortunately this is not an answer to your problem. What do you mean "by the time I get to PivotItems it all ends"? Do you mean the code finds no PivotItems and so does nothing, or is there an error? The former is exactly what happens to me when I try ChildItems. I do know that according to the VBA Help, ChildItems is "not available for OLAP data sources". Hope that helps at least. I'm working with a normal SQL datasource, and ChildItems doesn't seem to work. (If I remember right, when I was prototyping with an Excel datasource, it didn't work with that either!). PivotItems works fine. What is completely absent is any way of working with the OLAP hierarchy. The documentation in VBA help is confusing at best. I thought that ChildItems gave you a collection of the PivotItems that are "under" a higher-dimension PivotItem (e.g. using a geographical example, Edinburgh and Glasgow would be ChildItems in dimension "City" of "Scotland" in dimension "Country"). But some bits of the Help make dark hints (I can't say it's any clearer than that) that ChildItems and related properties only work if the items are "grouped". What does that mean? That they must be _manually_ grouped, rather than automatically grouped by the PivotTable? It's all very unclear. And surprisingly - because there always seems to be someone somewhere who knows the answer on these groups, especially with all the MVPs giving us the benefit of their knowledge - there is a deafening silence on this subject: no thread about ChildItems has ever ended with a definite answer. Can anyone enlighten us? meanwhile, iD, I'm sorry I don't have an Analysis Services cube to try to reproduce your problem - but give us some more detail and I may be able to help. cheers Seb iD wrote: Hi again Does anybody have even the slightest idea about this please? I can't find anything about it, not even on Microsoft's site. Many thanks iD iD wrote: Hi All I have a Pivot Table report (Excel 2003 sp1) connecting to a cube on SQL Server 2000 (sp 3a) and am trying to enumerate through a pivot field so that I can print a report for each item in it. On the worksheet there are 20 or so items selectable, but when I try to select one through code, the items count is 0. Below is a fragment of code that I am using (taken from other postings!): ' Dim pt As PivotTable Dim pf As PivotField Dim pi As PivotItem Set pt = ActiveSheet.PivotTables.Item("PivotTable1") ' this is ok as I can do pf.Name Set pf = pt.PivotFields("[Department]") ' this always displays 0 MsgBox pf.PivotItems.Count ' Does anyone have any sugestions please?!?! I have tried ChildItems and also working through CubeFields but by the time I get to PivotItems it all ends!!! Incidently ColumnFields seems ok, but I can't use this because of the way I have to format the report. Many thanks for any help/suggestions iD |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
PivotItems.Count is 0 when items are visible on the sheet
I've got almost the same problem recently,
and also failed to find an answer on Microsoft's site This solution I've tested only for PageFields (it's my issue) For example: when Pivot table is connected to OLAP source and dimension [SomePageField] is placed to PageArea of Excel XP Pivot table than: if Worksheets("Sheet1").PivotTables("PivotTable1").Cu beFields("[SomePageField]" ).EnableMultiplePageItems = True then Worksheets("Sheet1").PivotTables("PivotTable1").Pi votFields("[SomePageField] ").PivotItems.Count = 0 (however it's not expected to be empty according to VBA object model help) But Worksheets("Sheet1").PivotTables("PivotTable1").Pi votFields("[SomePageField] ").CurrentPageList is an ordinary VBA array of multiple selected Memebers of [SomePageField] dimension that is not empty (UBound is not 0) and can be looked through (and also set) in Vba So,in my case that is the way out IMHO -- Best regards, Yuriy "Seb" wrote in message oups.com... Hi iD to start with, unfortunately this is not an answer to your problem. What do you mean "by the time I get to PivotItems it all ends"? Do you mean the code finds no PivotItems and so does nothing, or is there an error? The former is exactly what happens to me when I try ChildItems. I do know that according to the VBA Help, ChildItems is "not available for OLAP data sources". Hope that helps at least. I'm working with a normal SQL datasource, and ChildItems doesn't seem to work. (If I remember right, when I was prototyping with an Excel datasource, it didn't work with that either!). PivotItems works fine. What is completely absent is any way of working with the OLAP hierarchy. The documentation in VBA help is confusing at best. I thought that ChildItems gave you a collection of the PivotItems that are "under" a higher-dimension PivotItem (e.g. using a geographical example, Edinburgh and Glasgow would be ChildItems in dimension "City" of "Scotland" in dimension "Country"). But some bits of the Help make dark hints (I can't say it's any clearer than that) that ChildItems and related properties only work if the items are "grouped". What does that mean? That they must be _manually_ grouped, rather than automatically grouped by the PivotTable? It's all very unclear. And surprisingly - because there always seems to be someone somewhere who knows the answer on these groups, especially with all the MVPs giving us the benefit of their knowledge - there is a deafening silence on this subject: no thread about ChildItems has ever ended with a definite answer. Can anyone enlighten us? meanwhile, iD, I'm sorry I don't have an Analysis Services cube to try to reproduce your problem - but give us some more detail and I may be able to help. cheers Seb iD wrote: Hi again Does anybody have even the slightest idea about this please? I can't find anything about it, not even on Microsoft's site. Many thanks iD iD wrote: Hi All I have a Pivot Table report (Excel 2003 sp1) connecting to a cube on SQL Server 2000 (sp 3a) and am trying to enumerate through a pivot field so that I can print a report for each item in it. On the worksheet there are 20 or so items selectable, but when I try to select one through code, the items count is 0. Below is a fragment of code that I am using (taken from other postings!): ' Dim pt As PivotTable Dim pf As PivotField Dim pi As PivotItem Set pt = ActiveSheet.PivotTables.Item("PivotTable1") ' this is ok as I can do pf.Name Set pf = pt.PivotFields("[Department]") ' this always displays 0 MsgBox pf.PivotItems.Count ' Does anyone have any sugestions please?!?! I have tried ChildItems and also working through CubeFields but by the time I get to PivotItems it all ends!!! Incidently ColumnFields seems ok, but I can't use this because of the way I have to format the report. Many thanks for any help/suggestions iD |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
PivotItems.Count is 0 when items are visible on the sheet
And how can I catch these currentpagelist ? Does this use array ?
"Yuri" wrote: I've got almost the same problem recently, and also failed to find an answer on Microsoft's site This solution I've tested only for PageFields (it's my issue) For example: when Pivot table is connected to OLAP source and dimension [SomePageField] is placed to PageArea of Excel XP Pivot table than: if Worksheets("Sheet1").PivotTables("PivotTable1").Cu beFields("[SomePageField]" ).EnableMultiplePageItems = True then Worksheets("Sheet1").PivotTables("PivotTable1").Pi votFields("[SomePageField] ").PivotItems.Count = 0 (however it's not expected to be empty according to VBA object model help) But Worksheets("Sheet1").PivotTables("PivotTable1").Pi votFields("[SomePageField] ").CurrentPageList is an ordinary VBA array of multiple selected Memebers of [SomePageField] dimension that is not empty (UBound is not 0) and can be looked through (and also set) in Vba So,in my case that is the way out IMHO -- Best regards, Yuriy "Seb" wrote in message oups.com... Hi iD to start with, unfortunately this is not an answer to your problem. What do you mean "by the time I get to PivotItems it all ends"? Do you mean the code finds no PivotItems and so does nothing, or is there an error? The former is exactly what happens to me when I try ChildItems. I do know that according to the VBA Help, ChildItems is "not available for OLAP data sources". Hope that helps at least. I'm working with a normal SQL datasource, and ChildItems doesn't seem to work. (If I remember right, when I was prototyping with an Excel datasource, it didn't work with that either!). PivotItems works fine. What is completely absent is any way of working with the OLAP hierarchy. The documentation in VBA help is confusing at best. I thought that ChildItems gave you a collection of the PivotItems that are "under" a higher-dimension PivotItem (e.g. using a geographical example, Edinburgh and Glasgow would be ChildItems in dimension "City" of "Scotland" in dimension "Country"). But some bits of the Help make dark hints (I can't say it's any clearer than that) that ChildItems and related properties only work if the items are "grouped". What does that mean? That they must be _manually_ grouped, rather than automatically grouped by the PivotTable? It's all very unclear. And surprisingly - because there always seems to be someone somewhere who knows the answer on these groups, especially with all the MVPs giving us the benefit of their knowledge - there is a deafening silence on this subject: no thread about ChildItems has ever ended with a definite answer. Can anyone enlighten us? meanwhile, iD, I'm sorry I don't have an Analysis Services cube to try to reproduce your problem - but give us some more detail and I may be able to help. cheers Seb iD wrote: Hi again Does anybody have even the slightest idea about this please? I can't find anything about it, not even on Microsoft's site. Many thanks iD iD wrote: Hi All I have a Pivot Table report (Excel 2003 sp1) connecting to a cube on SQL Server 2000 (sp 3a) and am trying to enumerate through a pivot field so that I can print a report for each item in it. On the worksheet there are 20 or so items selectable, but when I try to select one through code, the items count is 0. Below is a fragment of code that I am using (taken from other postings!): ' Dim pt As PivotTable Dim pf As PivotField Dim pi As PivotItem Set pt = ActiveSheet.PivotTables.Item("PivotTable1") ' this is ok as I can do pf.Name Set pf = pt.PivotFields("[Department]") ' this always displays 0 MsgBox pf.PivotItems.Count ' Does anyone have any sugestions please?!?! I have tried ChildItems and also working through CubeFields but by the time I get to PivotItems it all ends!!! Incidently ColumnFields seems ok, but I can't use this because of the way I have to format the report. Many thanks for any help/suggestions iD |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Controlling Pivotitems.visible | Excel Discussion (Misc queries) | |||
Pivot Change PivotItems - Visible/Hide with VBA | Excel Worksheet Functions | |||
Count Filtered Visible Items that Match Numeric Criteria between two ranges | Excel Worksheet Functions | |||
Excel Pivot Tables, Page Fields, Visible PivotItems, Activation | Excel Programming | |||
Add PivotItems to PivotTable / Enabling - Disabling PivotItems | Excel Programming |