Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

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


View products that this article applies to.

Summary

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

↑ Back to the top


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.

↑ Back to the top


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://msdn2.microsoft.com/en-us/library/aa188489(office.10).aspx

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

↑ Back to the top


Keywords: KB302101, kbofficewebpivot, kbhowto

↑ Back to the top

Article Info
Article ID : 302101
Revision : 6
Created on : 6/29/2007
Published on : 6/29/2007
Exists online : False
Views : 505