How To Retrieve A List of Filtered Members by Using the Office PivotTable Component (302101)


The information in this article applies to:

  • Microsoft Office XP Web Components
  • Microsoft Office 2003 Web Components
  • Microsoft Internet Explorer (Programming) 5
  • Microsoft Internet Explorer (Programming) 5.01
  • Microsoft Internet Explorer (Programming) 5.5
This article was previously published under Q302101

SUMMARY

This article demonstrates how you can determine which members on an axis of the Office PivotTable component have been included in a filter.

MORE INFORMATION

The Office PivotTable component includes a hidden member, PivotFilterUpdate, that has a StateOf property that you may check to determine the filtered state of a particular pivot member on an axis. The StateOf property for a pivot member returns one of the following values: plMemberStateClear (1), plMemberStateChecked (2) or plMemberStateGray (3). A pivot member is "cleared" if it is excluded from the filter, "checked" if the pivot member and all of its child members are included in the filter, or "gray" if only a portion of its child members are included in the filter.

NOTE: The PivotFilterUpdate object is a hidden member in the Office Web Components object model. This member is not documented and, therefore, is not supported by Microsoft Technical Support. The sample code that is contained in this article is provided "as-is".

Sample #1: Using an OLAP Data Source

This first sample illustrates how you can use recursion with the pivot members on an axis to either retrieve the state of each member and its child members or retrieve a list of only those members that have been included in the filter. This sample uses an OLAP data source (the FoodMart 2000 database that is included with Microsoft SQL Server Analysis Services version 8.0).
  1. Paste the following code in any text or HTML editor.

    Note The following code uses the Office XP version of the PivotTable object. For the Office 2003 version of the PivotTable object, replace the value clsid:0002E552-0000-0000-C000-000000000046 in the code with clsid:0002E55A-0000-0000-C000-000000000046. 
    <HTML>

<BODY>

<OBJECT ID="PTable" CLASSID="CLSID:0002E552-0000-0000-C000-000000000046" 
WIDTH="100%" HEIGHT="60%"><PARAM name="AutoFit" value="False"></PARAM>
</OBJECT>

<P/>

<TABLE WIDTH="100%" HEIGHT="35%">
 <TR>
  <TD WIDTH="35%" VALIGN="Top">
   <SELECT NAME="lstFieldSets" STYLE="width:'100%'">
    <OPTION VALUE="1" SELECTED>Time</OPTION>
    <OPTION VALUE="2">Store</OPTION>
    <OPTION VALUE="3">Product</OPTION>
   </SELECT>
   <P/>
   <BUTTON ID="Button1" STYLE="width:'100%'">List State of All Members
   </BUTTON>
   <P/>
   <BUTTON ID="Button2" STYLE="width:'100%'">List Selected Members Only
   </BUTTON>
  </TD>
  <TD WIDTH="65%">
   <TEXTAREA STYLE="width:'100%';height:'100%'" ID=txtResults>&#xa0;
   </TEXTAREA>
  </TD>
 </TR>
</TABLE>

<SCRIPT Language="VBScript">

Dim c   'For PivotTable constants.
Dim oView
Dim sResults
Dim aStates

Function Window_OnLoad()

   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, row, and filter axes.
   oView.ColumnAxis.InsertFieldSet oView.FieldSets("Time")
   oView.ColumnAxis.DisplayEmptyMembers = True
   oView.RowAxis.InsertFieldSet oView.FieldSets("Store")
   oView.RowAxis.DisplayEmptyMembers = True
   oView.FilterAxis.InsertFieldSet oView.FieldSets("Product")

   'Add a total to the data area.
   oView.DataAxis.InsertTotal oView.Totals("Store Sales")
   oView.Totals("Store Sales").NumberFormat = "_($* #,##0_)"
   
    aStates = Array("Cleared", "Checked", "Gray")

End Function

Function Button1_OnClick()
    Dim oChildren
    Dim sFS
    sFS = lstFieldsets.options(lstFieldsets.selectedIndex).Text
    Set oChildren = PTable.ActiveView.FieldSets(sFS).Member.ChildMembers
    sResults = "State of All Members: " & vbCrLf & vbCrLf
    For i = 0 To oChildren.Count - 1
        Recurse1 oChildren(i), 0
    Next
    txtResults.innerText = sResults
End Function

Function Recurse1(oPM, nLevel)
    Dim oFUpd 'OWC10.PivotFilterUpdate
    Set oFUpd = oPM.Field.FieldSet.CreateFilterUpdate
    sResults = sResults & aStates(oFUpd.StateOf(oPM)-1) & vbTab & _
               Space(nLevel * 3) & "- " & oPM.Name & vbCrLf
    For i = 0 To oPM.ChildMembers.Count - 1
         Recurse1 oPM.ChildMembers(i), nLevel + 1
    Next
End Function

Function Button2_OnClick()
    Dim oChildren
    Dim sFS
    sFS = lstFieldsets.options(lstFieldsets.selectedIndex).Text
    Set oChildren = PTable.ActiveView.FieldSets(sFS).Member.ChildMembers
    sResults = "Selected Members: " & vbCrLf & vbCrLf
    For i = 0 To oChildren.Count - 1
        Recurse2 oChildren(i)
    Next
    txtResults.innerText = sResults
End Function

Function Recurse2(oPM)
    Dim oFUpd 'OWC10.PivotFilterUpdate
    Dim nState
    Set oFUpd = oPM.Field.FieldSet.CreateFilterUpdate
    nState = oFUpd.StateOf(oPM)
    'If the member is checked, then we can assume that all of 
    'its child members are also checked and not drill down any further.
    If nState = c.plMemberStateChecked Then
        sResults = sResults & oPM.UniqueName & vbCrLf
    'If the member is gray, check to see which of its child members
    'are included.
    ElseIf nState = c.plMemberStateGray Then
        For i = 0 To oPM.ChildMembers.Count - 1
            Recurse2 oPM.ChildMembers(i)
        Next
    'If the member is cleared, there's no reason to
    'drill down any further.
    ElseIf nState = c.plMemberStateClear Then
    End If
End Function

</SCRIPT>

</HTML>
  2. In the PivotTable connection string, replace YourServer with a server that contains the FoodMart 2000 sample database.
  3. Save the file as C:\Filter1.htm.
  4. Start Internet Explorer and browse to C:\Filter1.htm.
  5. Once the PivotTable has connected to the data source, follow these steps:
    1. Apply a filter of your choice to any one of the axes.
    2. In the list, select the name of the fieldset that you filtered.
    3. Click List State of All Members. The resulting text in the text area lists all members and child members and indicates whether each is cleared, checked, or gray.
    4. Click List Selected Members Only. The resulting text in the text area lists the unique names of those members in the Fieldset that are selected, or included, in the filter.
  6. Repeat the previous step for the remaining axes members.

Sample #2: Using a Traditional Data Source

For a PivotTable that is bound to a traditional data source, you can use the approach described in the first sample to retrieve the filter state of members on an axis. However, when you use a traditional data source such as an Access database or a Microsoft Excel workbook, recursion is not needed to retrieve this information because pivot members do not have multiple child members. This second sample demonstrates how to retrieve the state of members without recursion.
  1. Paste the following code into any text or HTML editor.

    Note The following code uses the Office XP version of the PivotTable object. For the Office 2003 version of the PivotTable object, replace the value clsid:0002E552-0000-0000-C000-000000000046 in the code with clsid:0002E55A-0000-0000-C000-000000000046. 
    <HTML>

<BODY>

<OBJECT ID="PTable" CLASSID="CLSID:0002E552-0000-0000-C000-000000000046" 
WIDTH="100%" HEIGHT="60%"><PARAM name="AutoFit" value="False"></PARAM>
</OBJECT>

<P/>

<TABLE WIDTH="100%" HEIGHT="35%">
 <TR>
  <TD WIDTH="35%" VALIGN="Top">
   <SELECT NAME="lstFieldSets" STYLE="width:'100%'">
    <OPTION VALUE="1" SELECTED>Country</OPTION>
    <OPTION VALUE="2">Year</OPTION>
    <OPTION VALUE="3">ProductName</OPTION>
   </SELECT>
   <P/>
   <BUTTON ID="Button1" STYLE="width:'100%'">List State of All Members
   </BUTTON>
  </TD>
  <TD WIDTH="65%">
   <TEXTAREA STYLE="width:'100%';height:'100%'" ID=txtResults>&#xa0;
   </TEXTAREA>
  </TD>
 </TR>
</TABLE>

<SCRIPT Language="VBScript">

Dim c   'For PivotTable constants.
Dim oView

Function Window_OnLoad()

   Set c = PTable.Constants

    'Connect to the database and provide the commandtext for the rowset.
    PTable.ConnectionString = "Provider = Microsoft.Jet.OLEDB.4.0;" & _
     "Data Source = C:\Program Files\" & _
     "Microsoft Office\Office10\Samples\Northwind.mdb"
        
    PTable.CommandText = _
     "SELECT Orders.ShipCountry AS Country, (1-[Discount])*[Quantity]*" & _
     "[Order Details].[UnitPrice] AS OrderAmt, Year([OrderDate]) AS " & _
     "[Year], [Products].ProductName FROM (Orders INNER JOIN " & _
     "[Order Details] ON Orders.OrderID = [Order Details].OrderID) " & _
     "INNER JOIN Products ON [Order Details].ProductID = " & _
     "Products.ProductID"

    Set oView = PTable.ActiveView

    'Add fields to the row axis and column axis for grouping.
    oView.FilterAxis.InsertFieldSet oView.FieldSets("Country")
    oView.RowAxis.InsertFieldSet oView.FieldSets("ProductName")
    oView.ColumnAxis.InsertFieldSet oView.FieldSets("Year")

    'Add a total for the OrderAmt fieldset.
    oView.DataAxis.InsertTotal oView.AddTotal("SalesTotal", _
           oView.FieldSets("OrderAmt").Fields(0), c.plFunctionSum)
    oView.Totals("SalesTotal").NumberFormat = "$#,##0"

End Function

Function Button1_OnClick()

    Dim oChildren
    Dim sFS
    Dim oPM   'OWC10.PivotMember
    Dim oFUpd 'OWC10.PivotFilterUpdate
    Dim aStates

    aStates = Array("Cleared", "Checked")  'Possible states

    sFS = lstFieldsets.options(lstFieldsets.selectedIndex).Text
    Set oChildren = PTable.ActiveView.FieldSets(sFS).Member.ChildMembers
    sResults = "State of All Members: " & vbCrLf & vbCrLf
    
    For i = 0 To oChildren.Count - 1
        Set oPM = oChildren(i)
        Set oFUpd = oPM.Field.FieldSet.CreateFilterUpdate
        sResults = sResults & aStates(oFUpd.StateOf(oPM) - 1) & vbTab & _
               "- " & oPM.Caption & vbCrLf
    Next

    txtResults.innerText = sResults
End Function


</SCRIPT>

</HTML>
  2. In the PivotTable connection string, modify the path to Northwind.mdb to match your installation of Office.
  3. Save the file as C:\Filter2.htm.
  4. Start Internet Explorer and browse to C:\Filter2.htm.
  5. After the PivotTable has connected to the data source, follow these steps:
    1. Apply a filter of your choice to any one of the axes.
    2. In the list, select the name of the fieldset that you filtered.
    3. Click List State of All Members. The resulting text in the text area lists all members and indicates whether each is cleared or checked.
  6. Repeat the previous step for the remaining axes members.

REFERENCES

For additional information about the Office XP 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:kbhowto kbOfficeWebPivot KB302101 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.