How to filter many data in filter axis when using PiovtTable10 
Author Message
 How to filter many data in filter axis when using PiovtTable10

Dear all,

I know that we use the following code filter a rowaxis data

//*---------------

Sub TopThreeStores()

    Dim ptView
    Dim ptConstants
    Dim fldFilterField

    Set ptConstants = PivotTable1.Constants

    ' Set a variable to the active view of the PivotTable list.
    Set ptView = PivotTable1.ActiveView

    ' Set a variable to the field that is to be filtered.
    Set fldFilterField = PivotTable1.ActiveData.RowAxis.Fields("Store City")

    ' Filter the stores based on profit.
    Set fldFilterField.FilterOn = ptView.Totals("Profit")

    ' Set the function used to filter the stores.
    fldFilterField.FilterFunction = ptConstants.plFilterFunctionTopCount

    ' Display the three most profitable stores.
    fldFilterField.FilterFunctionValue = 3

End Sub

//*-------------------

In PivotTable9 we use the following code

//*---------
Set pview = PivotTable1.ActiveView
Set c = PivotTable1.Constants

' Include only the following four members in the Promotions field
Set fld = pview.FieldSets("Promotions").Fields(0)
fld.FilterFunction = c.plFilterFunctionInclude
fld.FilterMembers = Array("Cash Register Lottery", _
                          "Free For All", _
                          "Price Savers", _
                          "Two for One")
//*--------------------

but I have a data A,B,C,D in filter AXis, I want to need B,D, Do anyone
know how to do it?

Thank in advance!



Mon, 25 Oct 2004 17:27:34 GMT
 How to filter many data in filter axis when using PiovtTable10

The code you show is not equivilent, the first shows conditional filtering
(Top/Bottom n) and the second shows include/exclude filtering. OWC10
introduced conditional filtering, changed the properties used to set
include/exclude filtering and allowed multi-member filtering on the filter
axis. The same include/exclude filtering settings are used on the filter
axis as the other axes, unlike in OWC9 which has a seperate property.
Conditional filtering is only applied on the row and column axes, not the
filter axis.

I'm guessing that you want to know how to do include/exclude filtering in
OWC10, specifically how would the OWC9 code you have shown be written for
OWC10.

The easiest way to understand the OWC10 include/exclude filtering is to
consider a filter drop-down, with the tree expanded to show all members and
with all members checked. Now, to get the filtering you want you will
uncheck and check different members, for example, you may uncheck the all
member and then check a small number of members you want to include. In fact
for any final combination of members, no matter how complex, you can come up
with a minimum number of mouse clicks needed to get what you want. For
example, if you want all of 2000 except for Janurary plus quarter three of
2001 except for the first 3 days, you would uncheck the all member, check
the 2000 member, then uncheck Jan, then check quarter3 under 2001 and
uncheck the first few days.

The include/exclude filtering is recorded in much the same way. Each mouse
click that unchecks a member is a excluded member and each click that checks
a member is an included member. Each PivotField has a list of included and
excluded members (PivotField.IncludedMembers, PivotField.ExcludedMembers).
The one special case is the all member which is set using the
PivotFieldSet.AllIncludeExclude property which can be set to include,
exclude and also to default where default indicates that you want the
default filtering which is all members when on the row and column axes and
the default member when on the filter axis.

Now we have some understanding of the properties, coding should be easy
(Warning untested, uncompiled code):

Set pview = PivotTable1.ActiveView
Set c = PivotTable1.Constants

 ' Include only the following four members in the Promotions field

Set fldset = pview.FieldSets("Promotions")
' Uncheck the all member
fldset.AllIncludeExclude = c.plAllExclude

Set fld = pview.FieldSets("Promotions").Fields(0)
' Check the members you want.
fld.IncludedMembers = Array("Cash Register Lottery", _
                           "Free For All", _
                           "Price Savers", _
                           "Two for One")

Hope this helps you (and anyone else listening).

--
This posting is provided "AS IS" with no warranties, and confers no rights.


Quote:
> Dear all,

> I know that we use the following code filter a rowaxis data

> //*---------------

> Sub TopThreeStores()

>     Dim ptView
>     Dim ptConstants
>     Dim fldFilterField

>     Set ptConstants = PivotTable1.Constants

>     ' Set a variable to the active view of the PivotTable list.
>     Set ptView = PivotTable1.ActiveView

>     ' Set a variable to the field that is to be filtered.
>     Set fldFilterField = PivotTable1.ActiveData.RowAxis.Fields("Store
City")

>     ' Filter the stores based on profit.
>     Set fldFilterField.FilterOn = ptView.Totals("Profit")

>     ' Set the function used to filter the stores.
>     fldFilterField.FilterFunction = ptConstants.plFilterFunctionTopCount

>     ' Display the three most profitable stores.
>     fldFilterField.FilterFunctionValue = 3

> End Sub

> //*-------------------

> In PivotTable9 we use the following code

> //*---------
> Set pview = PivotTable1.ActiveView
> Set c = PivotTable1.Constants

> ' Include only the following four members in the Promotions field
> Set fld = pview.FieldSets("Promotions").Fields(0)
> fld.FilterFunction = c.plFilterFunctionInclude
> fld.FilterMembers = Array("Cash Register Lottery", _
>                           "Free For All", _
>                           "Price Savers", _
>                           "Two for One")
> //*--------------------

> but I have a data A,B,C,D in filter AXis, I want to need B,D, Do anyone
> know how to do it?

> Thank in advance!



Fri, 29 Oct 2004 16:10:09 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. How detect filter selected in filter axis?

2. Help, Multiple Filters Using the Filter Property

3. Dynamic Filter on TCustomADODataset where filter field data type change

4. Saving User custom filters or Exporting filtered data

5. Filtering Pivottable on the row axis

6. Limit the values in filter axis

7. Filter Accross Date Range - Different Filter Value for Different Dates

8. Row Filter & Join Filter

9. Merge Replication with Dynamic Filter and Row Filter failure on two Deletes

10. Row filters don't filter enough

11. Data not copied in snapshot when using dynamic filter with host_name()

12. Problem with filters and join filters in merge replication


 
Powered by phpBB® Forum Software