PivotTable: ChildItems and ParentItem don't work

[Seb Thirlway]

Hi

I'm programming creation and formatting of Excel pivot-tables, using
an Excel datasource.
I have three dimensions as Row fields in the pivot table. The top
dimension (ClientType) has two members: Segregated and Non-Seg. I'd
like to hide the third level of detail for all items (Clients) which
are children of the Segregated top-level member. I thought this code
would do the trick:

Dim objClient As Excel.PivotItem
For Each objClient In
Worksheets("Value").PivotTables("PTValue").PivotFields("ClientType").PivotItems("Seg").ChildItems
objClient.HideDetail
Next objClient

This runs without error, but doesn't do anything! If I break in this
procedure it's obvious why nothing is happening - I ask

? Worksheets("Value").PivotTables("PTValue").PivotFields("ClientType").PivotItems("Seg").ChildItems.Count

in the Immediate window, and get 0. Excel thinks there are no
ChildItems, when clearly there are (3 of them).
I thought I'd try to use the second-level (Client)'s ParentItem
property instead - iterate through all Clients, and if the ParentItem
is "Seg", then do a HideDetail. Here's the code:

Dim objClient As Excel.PivotItem
For Each objClient In
Worksheets("Value").PivotTables("PTValue").PivotFields("Policy").PivotItems
-> If objClient.ParentItem.Name = "Seg" Then objClient.ShowDetails
= False
Next objClient

But the line marked -> gives an error: 1004 "Unable to get the
ParentItem property of the PivotItem class". I check objClient's Name
in the Immediate window to check I've got the PivotItem I think I
should - I look at the pivottable, and that item clearly DOES have a
ParentItem.

There's no documentation of this problem anywhere (Microsoft or
Google). What is going on? I suspect that the ChildItems and
ParentItem properties simply don't work, but since few people use them
no-one's documented it.

Anyone got any ideas? Thanks!




Seb Thirlway

 

[Seb Thirlway]

Well OK, I didn't actually do

objClient.HideDetail

that would be stupid, as there is no HideDetail method. I did

objClient.ShowDetail=False

Just a brain-bug when I composed the post.

However, ChildItems still doesn't work. Are there any MVPs or MS
people reading this newsgroup? Please respond! I'd create a simple
test example and send it in to MS as a bug report if I could find a
way to on the MS site.

The Help mentions "returns ...[a collection of all the items]... which
are GROU CHILDREN..."
Does this mean it only works on EXCEL groupings? I assumed it worked
on the Pivot table hierarchy, i.e. (to use the classic geographical
example for OLAP structures):

Country Town
Scotland Edinburgh
Scotland Glasgow
England Newcastle
England Manchester

If the Pivot table has Country, and then Town as Rowfields (in that
order), then PivotFields("Country").PivotItem("Scotland").ChildItems
should return a collection containing the two PivotItems "Edinburgh"
and "Glasgow".

Is this correct, or does it only work on Excel, as opposed to
Pivot-table groupings?

Even if someone could just tell me authoritatively that it simply
doesn't work, that would be helpful, as I could stop trying to make it
work.

thanks


Seb

 

[blairkelly1981]

Hi - a long shot

This really is a long shot but are you seb who previously worked at F&C (front office databases ringing bells?). I know this post is years old but so far it's my only possible lead to your whereabouts.

Fingers very tightly crossed. (god damb loosing your e-mail!)