Microsoft KB Archive/302101

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

Article ID: 302101

Article Last Modified on 6/29/2007

-

APPLIES TO


 * Microsoft Office Web Components
 * Microsoft Office Web Components
 * Microsoft Internet Explorer 5.0
 * Microsoft Internet Explorer 5.01
 * Microsoft Internet Explorer 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 &quot;cleared&quot; if it is excluded from the filter, &quot;checked&quot; if the pivot member and all of its child members are included in the filter, or &quot;gray&quot; 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 &quot;as-is&quot;.

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).   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. 



 





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 = _ &quot;provider=msolap;data source=YourServer;inital catalog=Foodmart 2000;&quot; PTable.DataMember = &quot;Sales&quot;

Set oView = PTable.ActiveView 'Add Dimensions to the column, row, and filter axes. oView.ColumnAxis.InsertFieldSet oView.FieldSets(&quot;Time&quot;) oView.ColumnAxis.DisplayEmptyMembers = True oView.RowAxis.InsertFieldSet oView.FieldSets(&quot;Store&quot;) oView.RowAxis.DisplayEmptyMembers = True oView.FilterAxis.InsertFieldSet oView.FieldSets(&quot;Product&quot;)

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

End Function

Function Button1_OnClick Dim oChildren Dim sFS sFS = lstFieldsets.options(lstFieldsets.selectedIndex).Text Set oChildren = PTable.ActiveView.FieldSets(sFS).Member.ChildMembers sResults = &quot;State of All Members: &quot; & 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) & &quot;- &quot; & 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 = &quot;Selected Members: &quot; & 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



  In the PivotTable connection string, replace YourServer with a server that contains the FoodMart 2000 sample database.</li> Save the file as C:\Filter1.htm.</li> Start Internet Explorer and browse to C:\Filter1.htm.</li> Once the PivotTable has connected to the data source, follow these steps: <ol style="list-style-type: lower-alpha;"> Apply a filter of your choice to any one of the axes.</li> In the list, select the name of the fieldset that you filtered.</li> 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.</li> 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.</li></ol> </li> Repeat the previous step for the remaining axes members.</li></ol>

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. <ol>  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. 

<BODY>

<OBJECT ID=&quot;PTable&quot; CLASSID=&quot;CLSID:0002E552-0000-0000-C000-000000000046&quot; WIDTH=&quot;100%&quot; HEIGHT=&quot;60%&quot;></PARAM> </OBJECT>

<P/>

<SCRIPT Language=&quot;VBScript&quot;>

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 = &quot;Provider = Microsoft.Jet.OLEDB.4.0;&quot; & _ &quot;Data Source = C:\Program Files\&quot; & _ &quot;Microsoft Office\Office10\Samples\Northwind.mdb&quot; PTable.CommandText = _ &quot;SELECT Orders.ShipCountry AS Country, (1-[Discount])*[Quantity]*&quot; & _ &quot;[Order Details].[UnitPrice] AS OrderAmt, Year([OrderDate]) AS &quot; & _ &quot;[Year], [Products].ProductName FROM (Orders INNER JOIN &quot; & _    &quot;[Order Details] ON Orders.OrderID = [Order Details].OrderID) &quot; & _ &quot;INNER JOIN Products ON [Order Details].ProductID = &quot; & _ &quot;Products.ProductID&quot;

Set oView = PTable.ActiveView

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

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

End Function

Function Button1_OnClick

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

aStates = Array(&quot;Cleared&quot;, &quot;Checked&quot;) 'Possible states

sFS = lstFieldsets.options(lstFieldsets.selectedIndex).Text Set oChildren = PTable.ActiveView.FieldSets(sFS).Member.ChildMembers sResults = &quot;State of All Members: &quot; & 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 & _ &quot;- &quot; & oPM.Caption & vbCrLf Next

txtResults.innerText = sResults End Function

</SCRIPT>

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

<div class="references_section">