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).
- 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> 
</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>
- In the PivotTable connection string, replace YourServer with a server that contains the FoodMart 2000 sample
database.
- Save the file as C:\Filter1.htm.
- Start Internet Explorer and browse to
C:\Filter1.htm.
- Once the PivotTable has connected to the data source,
follow these steps:
- Apply a filter of your choice to any one of the
axes.
- In the list, select the name of the fieldset that you
filtered.
- 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.
- 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.
- 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.
- 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> 
</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>
- In the PivotTable connection string, modify the path to
Northwind.mdb to match your installation of Office.
- Save the file as C:\Filter2.htm.
- Start Internet Explorer and browse to
C:\Filter2.htm.
- After the PivotTable has connected to the data source,
follow these steps:
- Apply a filter of your choice to any one of the
axes.
- In the list, select the name of the fieldset that you
filtered.
- Click List State of All Members. The resulting text in the text area lists all members and
indicates whether each is cleared or checked.
- Repeat the previous step for the remaining axes
members.