PRB: Incorrect ChildMembers.Count for PivotMember Object (301582)


The information in this article applies to:

  • Microsoft Office 2003 Web Components
  • Microsoft Office XP Web Components
This article was previously published under Q301582

SYMPTOMS

When you use the ChildMembers property of a PivotMember object to retrieve a collection of child members, the child members collection is empty and the Count property returns zero.

CAUSE

When you retrieve a PivotMember object by calling one of the following methods or properties, the PivotMember object that is returned may be "cloned":
  • FindMember method
  • IncludedMembers property
  • ExcludedMembers property
  • OrderedMembers property
Cloned members are disconnected; due to the manner in which the PivotTable component internally handles disconnected members, you may be unable to retrieve the ChildMembers collection for a cloned member. Likewise, if you retrieve the ParentMember for a cloned PivotMember, you may also be unable to retrieve the ChildMembers for that ParentMember.

RESOLUTION

To work around this problem, obtain a reference to the PivotMember by calling the FindMember method using the path for that member. This workaround is illustrated in the More Information section.

MORE INFORMATION

  1. Paste the following code in any text or HTML editor:
    <HTML>

<BODY>

<button id="MyButton1">#1</button>
<P/>
<button id="MyButton2">#2</button>
<P/>
<object classid=clsid:0002E552-0000-0000-C000-000000000046 
id="PTable"></object>


</BODY>

<SCRIPT language=vbscript>

Dim c      'For PivotTable constants
Dim oView 

Function Window_OnLoad()

   PTable.AutoFit = True
   Set c = PTable.Constants

   'Connect to the data source.
   PTable.ConnectionString = _
     "provider=msolap;data source=YourServer;inital catalog=Foodmart 2000;"
   PTable.DataMember = "Sales"

   Set oView = PTable.ActiveView

   'Add Dimensions to the column and filter axes.
   oView.ColumnAxis.InsertFieldSet oView.FieldSets("Time")
   oView.ColumnAxis.FieldSets("Time").Fields("Year").Expanded = True
   oView.RowAxis.InsertFieldSet oView.FieldSets("Store")
   oView.RowAxis.FieldSets("Store").Fields("Store Country").Expanded = True
      
   'Add a total to the data area.
   oView.DataAxis.InsertTotal oView.Totals("Store Sales")
   oView.Totals("Store Sales").NumberFormat = "_($* #,##0_)"

End Function   
    
Function MyButton1_OnClick()

    Dim oPM, oPM2
    Set oPM = oView.FieldSets("Store").Member.ChildMembers("USA")
    Set oPM2 = oView.FieldSets("Store").FindMember(oPM.UniqueName)
    MsgBox "There are " & oPM2.ChildMembers.Count & " Child Members in " _
           & oPM2.UniqueName
    
End Function

Function MyButton2_OnClick()

    Dim oPM, oPM2
    'The next line returns the member [Store].[All Stores].[USA]
    Set oPM = oView.FieldSets("Store").Member.ChildMembers("USA")
    Set oPM2 = oView.FieldSets("Store").FindMember( _ 
           oPM.Path(c.plFindFormatPathName), c.plFindFormatPathName)
    MsgBox "There are " & oPM2.ChildMembers.Count & " Child Members in " _
           & oPM2.UniqueName
    
End Function
</SCRIPT>
</HTML>
  2. In the connection string for the PivotTable, replace "YourServer" with the name of an Analysis Server that contains the Foodmart 2000 sample database.
  3. Save the file as C:\Test.htm.
  4. Start Internet Explorer and browse to C:\Test.htm.
  5. After the Web page is rendered and the PivotTable is loaded with data, click #1. A message box displays and indicates an incorrect number of child members for [Store].[All Stores].[USA]. Click OK to dismiss the message box.
  6. Click #2. A message box displays and indicates the correct number of child members for [Store].[All Stores].[USA]. Click OK to dismiss the message box.

REFERENCES

For additional information about Office Web Components, see the following Microsoft Developer Network (MSDN) Web sites:

Microsoft Office Development with Visual Studio
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnoxpta/html/vsofficedev.asp

Microsoft Office Development Center
http://msdn.microsoft.com/office

Modification Type:MajorLast Reviewed:5/5/2006
Keywords:kbOfficeWebPivot kbprb KB301582
©2004 Microsoft Corporation. All rights reserved.