PivotItems.Count is 0 when items are visible on the sheet

[iD]

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

 

[iD]

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

 

[Seb]

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

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

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

 

[Yuri]

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").CubeFields("[SomePageField]"
).EnableMultiplePageItems = True
then

Worksheets("Sheet1").PivotTables("PivotTable1").PivotFields("[SomePageField]
").PivotItems.Count = 0
(however it's not expected to be empty according to VBA object model help)

But


Worksheets("Sheet1").PivotTables("PivotTable1").PivotFields("[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

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

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

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

 

[Microlong]

And how can I catch these currentpagelist ? Does this use array ?

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").CubeFields("[SomePageField]"
).EnableMultiplePageItems = True
then

Worksheets("Sheet1").PivotTables("PivotTable1").PivotFields("[SomePageField]
").PivotItems.Count = 0
(however it's not expected to be empty according to VBA object model help)

But


Worksheets("Sheet1").PivotTables("PivotTable1").PivotFields("[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

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

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