Export (0) Print
Expand All

Filtering Data Programmatically in Excel 2007 by Using PivotTables

Office 2007

Summary: Learn how to use new elements of the filtering object model and new filter types in Microsoft Office Excel 2007, and find out how to apply filters to Online Analytical Processing (OLAP) and relational Microsoft PivotTable dynamic reports by using Microsoft Visual C#. (15 printed pages)

When you are working with large amounts of data in Microsoft Office Excel 2007, you must be able to quickly see what you want, when you want it. By filtering the data in Excel 2007, you can hide the values that are not required for the detail the user wants to display. This article describes the power of applying filters through code. It shows how to programmatically implement filtering in Excel 2007 PivotTables and describes several improvements to the Excel 2007 user interface.

In Microsoft Office Excel 2007, the filter drop-down tree view specifies drill status. That is, it specifies the data that is selected for display in the Filtering dialog box. You can navigate through the levels of data and select only the relevant data that you want to see. Table 1 lists the possible states that you see when you work with filters in Excel 2007.

Table 1. Filtering states in Excel 2007

Filtering states

Description

Checked

The item and all descendants are included in the filter.

Cleared

The item and all descendants are excluded by the filter.

Mixed

The item in the filter and at least one descendant is excluded by the filter.

Figure 1 shows a typical Excel 2007 PivotTable report that has a mixed filtering status.

Figure 1. PivotTable report showing filter states where Drink is mixed, Food is checked, and Non-Consumable is cleared

PivotTable report with mixed filtering status

In Excel 2007 PivotTables, you can apply multiple filters to a field:

  • Manual filter: Applied by selecting from a list of available values.

  • Value filter: Based on the value of an item in the data (for example, displaying data that is larger than the value "2000").

  • Label filter: Based on the contents of the label. Enables you to filter labels that contain a specific word or phrase (for example, displaying product names that contain the word "car").

  • Date filter: Based on time spans. Could be equal to a particular date, range of dates, or larger than or less than a specified date (for example, displaying only dates in the current year).

Adding New Filter Types Programmatically

You can use the Add method to add new filters to the PivotFilters collection. The syntax and arguments for the Add method are as follows.

Add(Type, [DataField], [DataCubeField], [Value1], [Value2],[Order],[Name],[Description],[MemberPropertyField])

Depending on the filter type that you want to add, different arguments are either required or not available. For more information about filter types, the arguments that must be supplied, and the arguments that are unavailable for each filter type, see FilterType property.

Examples

When you programmatically add filter types, the results set may be statistically driven or based on the data caption. Here are some examples of results and the statements that produce those results:

  • This statement filters the top 10 values of sales.

    ActiveCell.PivotField.PivotFilters.Add FilterType := xlTopCount DataField := Sales Value1 := 10;

  • This statement filters the results where the data caption does not fall alphabetically between "A" and "G".

    ActiveCell.PivotField.PivotFilters.Add FilterType := xlCaptionIsNotBetween Value1 := “A” Value2 := “G”;

  • This statement filters on values where employee hours are larger than or equal to 55 for the time period.

    ActiveCell.PivotField.PivotFilters.Add FilterType := xlValueIsGreaterThanOrEqualTo DataField := Hours Value1 := 55;

The following example returns a run-time error because the data type of Value1 is not valid. The Value1 field is expecting an integer.

ActiveCell.PivotField.PivotFilters.Add FilterType := xlValueIsGreaterThanOrEqualTo DataField := Sales Value1 := “Allan”;

The following examples show how to programmatically filter and display filtering in Excel 2007 with C#:

  • In the first example, an AdvancedFilter method applies a label, value, or date filter to a field in a PivotTable, and then verifies whether it was applied.

  • The second example applies a manual filter to a field in an Online Analytical Processing (OLAP) PivotTable.

  • The third example applies a manual filter to a field in a native (relational) PivotTable. You can see the full code examples in the download available with this article.

This example shows how to apply a filter to a field in a PivotTable and then determine whether the filter was applied.

First, set up the environment and new instance of you will be working with, where xlPIA is a reference to the Excel 2007 interop assembly and xlApp represents the new application. Figure 2 shows the connected PivotTable before the filters are applied.

Figure 2. Unfiltered PivotTable

Unfiltered PivotTable

Next, establish the object and start the application.

using System;
using System.Collections.Generic;
using System.Text;
using xlPIA = Microsoft.Office.Interop.Excel;
namespace LaunchXL
{
    class Program
    {
static void Main(string[] args)
    {
    xlPIA.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

Then, make the application status visible and call the procedure that applies the filters.

xlApp.Visible = true;

FilteringInOlapPivots(xlApp);
FilteringInRelationalPivots(xlApp);

Next, establish that the constant string connectionString uses the OLE DB provider and points to the Northwind database. Some other variables that are identified include tableName, workbook (xlWB), worksheet (xlWS), and range (xlRG).

    const string connectionString = "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=xlextdat8;Client Cache Size=25;Auto Synch Period=10000;Initial Catalog=Northwind";
    const string tableName = "Invoices";

    xlWB = xlApp.Workbooks.Add(miss);
    xlWS = (xlPIA.Worksheet)xlWB.Worksheets[1];
    xlRG = xlWS.get_Range("A3", miss);

Then you pass the connectionString to the PivotCache object (xlPC), establishing the connection with the Invoices table of the Northwind database.

    xlPC = (xlPIA.PivotCache)((xlPIA.PivotCaches)xlWB.PivotCaches()).Create(xlPIA.XlPivotTableSourceType.xlExternal, miss, xlPIA.XlPivotTableVersionList.xlPivotTableVersion12);
    xlPC.Connection = connectionString;
    xlPC.CommandType = xlPIA.XlCmdType.xlCmdTable;
    xlPC.CommandText = tableName;
    xlPC.MaintainConnection = true;
    xlPT = (xlPIA.PivotTable)xlPC.CreatePivotTable(
        xlRG.get_Address(miss, miss, xlPIA.XlReferenceStyle.xlR1C1, miss, miss),
        "RelationalPivot",
        miss,
        xlPIA.XlPivotTableVersionList.xlPivotTableVersion12);

Next, populate the PivotTable by adding Country and City to the row area of the PivotTable. The Position argument dictates where it will be located in the row area of the PivotTable.

    ((xlPIA.PivotField)xlPT.PivotFields("Country")).Orientation = xlPIA.XlPivotFieldOrientation.xlRowField;
    ((xlPIA.PivotField)xlPT.PivotFields("Country")).Position = 1;

    ((xlPIA.PivotField)xlPT.PivotFields("City")).Orientation = xlPIA.XlPivotFieldOrientation.xlRowField;
    ((xlPIA.PivotField)xlPT.PivotFields("City")).Position = 2;

Add the UnitPrice field to the data area of the PivotTable, and apply the Sum function.

    xlPT.AddDataField(xlPT.PivotFields("UnitPrice"), "Sum of UnitPrice", xlPIA.XlConsolidationFunction.xlSum);

Apply a value filter to the PivotTable for the Country field and data where UnitPrice is between $7,000 and $15,000.

AdvancedFilter(
    xlPT, 
    (xlPIA.PivotField)xlPT.PivotFields("Country"), 
    xlPIA.XlPivotFilterType.xlValueIsBetween, 
    xlPT.PivotFields("Sum of UnitPrice"), 
        7000, 
        15000, 
        1, 
        "PivotBetweenFilter", 
        "Filter on Country by Sum of UnitPrice", 
        null, 
        (xlPIA.Worksheet)xlWB.Worksheets[2]);

Clear all the existing filters in the PivotTable.

xlPT.ClearAllFilters();

Create a connection to the OLAP data source by setting the PivotCache and the connection, and applying the version of the PivotTable.

    xlWB.Connections.Add(connectionName, "", connectionString, commandText, 1);
    xlPC= ((xlPIA.PivotCaches)xlWB.PivotCaches()).Create(xlPIA.XlPivotTableSourceType.xlExternal, xlWB.Connections[connectionName], xlPIA.XlPivotTableVersionList.xlPivotTableVersion12);
    
    xlPT = (xlPIA.PivotTable)xlPC.CreatePivotTable(
    xlRG.get_Address(miss, miss, xlPIA.XlReferenceStyle.xlR1C1, miss, miss),
    "OLAPPivot",
    miss,
    xlPIA.XlPivotTableVersionList.xlPivotTableVersion12);

Populate the PivotTable and add the Customer Geography field to the Report Filter area of the PivotTable.

    ((xlPIA.CubeField)xlPT.CubeFields["[Customer].[Customer Geography]"]).Orientation = xlPIA.XlPivotFieldOrientation.xlPageField;
    ((xlPIA.CubeField)xlPT.CubeFields["[Customer].[Customer Geography]"]).Position = 1;

Add the Product Categories field to the Row area of the PivotTable.

    ((xlPIA.CubeField)xlPT.CubeFields["[Product].[Product Categories]"]).Orientation = xlPIA.XlPivotFieldOrientation.xlRowField;
    ((xlPIA.CubeField)xlPT.CubeFields["[Product].[Product Categories]"]).Position = 1;

Add the Internet Sales Amount to the data area in the PivotTable.

    xlPT.AddDataField(xlPT.CubeFields["[Measures].[Internet Sales Amount]"], "Internet Sales Amount", miss);

    xlWB.Worksheets.Add(miss, xlWB.Worksheets[3], miss, miss);
    xlWS.Name = "Olap Pivot";
    xlWS.Activate();
    ((xlPIA.Worksheet)xlWB.Worksheets[2]).Name = "Value filter info";
    ((xlPIA.Worksheet)xlWB.Worksheets[3]).Name = "Manual filter info";
    ((xlPIA.Worksheet)xlWB.Worksheets[4]).Name = "Label filter info";
    xlPT.AllowMultipleFilters = true;

    xlPF = (xlPIA.PivotField)xlPT.PivotFields("[Product].[Product Categories].[Category]");

Expand the applied items by setting the DrilledDown property to True.

xlPF.DrilledDown = true;

Apply a manual filter to the Category field.

string[] items = {"[Product].[Product Categories].[Category].&[1]"};
OlapManualFilter(xlPF,items,(xlPIA.Worksheet)xlWB.Worksheets[3]);
    xlPF = (xlPIA.PivotField)xlPT.PivotFields("[Product].[Product Categories].[Subcategory]");

Expand the applied items to set the drill state of the PivotTable.

xlPF.DrilledDown = true;

AdvancedFilter(
    xlPT,
    xlPF, 
    xlPIA.XlPivotFilterType.xlTopCount, 
    (xlPIA.CubeField)xlPT.CubeFields["[Measures].[Internet Sales Amount]"], 
    2,
    null, 
    1, 
    "PivotTopNFilter", 
    "Top 2 Filter on subcategory", 
    null, 
    (xlPIA.Worksheet)xlWB.Worksheets[2]);

Next, apply a Label filter on the Product Name field.

    AdvancedFilter(
    xlPT,
    (xlPIA.PivotField)xlPT.PivotFields("[Product].[Product Categories].[Product Name]"),
    xlPIA.XlPivotFilterType.xlCaptionContains,
    null,
    "Silver",
    null,
    -1,
    "PivotLabelFilter",
    "Label filter contains silver on member property field",
    (xlPIA.PivotField)xlPT.PivotFields("[Product].[Product Categories].[Product Name].[Color]"),
    (xlPIA.Worksheet)xlWB.Worksheets[4]);

    xlWB.Close(false, miss, miss);
    }

        public static void AdvancedFilter(xlPIA.PivotTable xlPT, xlPIA.PivotField xlPF, xlPIA.XlPivotFilterType xlPivotFilterType, object dataField, object value1, object value2, int order, string name, string description, xlPIA.PivotField memberPropertyField, xlPIA.Worksheet outputSheet)
    {
    xlPIA.PivotFilter xlPivotFilter;
  
    int numFilters;

Set up the filter for the PivotTable, including the PivotTable property, to determine whether multiple filters are enabled.

xlPivotFilter = xlPF.PivotFilters.Add(
    xlPivotFilterType,
    dataField,
    value1,
    value2,
    order,
    name,
    description,
    memberPropertyField);

    if (xlPT.AllowMultipleFilters == true) 
    {
    outputSheet.get_Range("A1", Type.Missing).Value2 = "Allow multiple filters is set to true";
    }
    else
    {
    outputSheet.get_Range("A1", Type.Missing).Value2 = "Allow multiple filters is set to false";
    }

Get the number of filters applied on a field. This does not include manual filters: It includes value, label, and date filters.

numFilters = ((xlPIA.PivotFilters)xlPF.PivotFilters).Count;
outputSheet.get_Range("A2", Type.Missing).Value2 = "Number of filters applied " + numFilters;

Get information about the specific filter (Item(1)) that is applied to a pivot filter from the PivotField.PivotFilters collection, and write the results to the applied range in the workbook.

    xlPivotFilter = (xlPIA.PivotFilter)xlPF.PivotFilters.get_Item(1);

    outputSheet.get_Range("A3", Type.Missing).Value2 = "Filter is applied on the following pivot field: " + xlPivotFilter.PivotField.Name;

Gather the filter type from the filter object and write the results to the applied range in the workbook.

outputSheet.get_Range("A4", Type.Missing).Value2 = "The filter type is: " + xlPivotFilter.FilterType;

Verify that the PivotTable is connected to an OLAP data source. If it is connected, output that it has a value filter applied in addition to the name of the measure. If it is not connected, output a message that indicates that that you are working with a non-OLAP PivotTable.

    if ((xlPT.PivotCache()).OLAP == true)
    {
        outputSheet.get_Range("A5", Type.Missing).Value2 = "This is an OLAP Pivot with a value filter applied and the measure name is: " + ((xlPIA.CubeField)xlPivotFilter.DataCubeField).Name;
    }
    else
    {
        outputSheet.get_Range("A5", Type.Missing).Value2 = "This is a non-OLAP Pivot with a value filter applied and the measure name is: " + ((xlPIA.PivotField)xlPivotFilter.DataField).Name;

    }
}//

Next, output the filter order. For a label or date filter, this value is 1. The Tdis property is useful when there are multiple value filters applied on the PivotTable because it lets you determine the order in which value filters are applied.

outputSheet.get_Range("A10", Type.Missing).Value2 = "Filter Order: " + xlPivotFilter.Order;

For an OLAP PivotTable, you can verify that the filter is applied to a member property field. By using the following if statement, you output a message that indicates that the filter is applied to member properties.

if (xlPivotFilter.IsMemberPropertyFilter)
{
    outputSheet.get_Range("A11", Type.Missing).Value2 = "Filter is applied on the following member property field: " + xlPivotFilter.MemberPropertyField.Name;
}
}

Figure 3 shows the PivotTable with this filtering applied.

Figure 3. Filtered Country and Sum of Amount between $7000 and $15000

Filtered PivotTable

Applying a Manual Filter to a Pivot Field Using C#

The next example shows how to apply a manual filter to a PivotField object.

/// <param name="xlPF">PivotField to apply filtering on</param>
/// <param name="items">Array of item names that will be filtered.</param>

    public static void RelationalManualFilter(xlPIA.PivotField xlPF, string[] items, xlPIA.Worksheet outputSheet)
    {

First, you filter out items by setting the item Visible property to False.

foreach (string item in items)
{
    ((xlPIA.PivotItem)(xlPF.PivotItems(item))).Visible = false; 
}

    outputSheet.get_Range("A1", Type.Missing).Value2 = "Manual filter information for the following pivot field: " + xlPF.Name;

Then you get a count of visible items that are now in the VisibleItems list.

outputSheet.get_Range("A2", Type.Missing).Value2 = "Number of visible items: " + ((xlPIA.PivotItems)xlPF.get_VisibleItems(Type.Missing)).Count; 

Next, set the number of hidden items from the HiddenItems list.

    outputSheet.get_Range("A3", Type.Missing).Value2 = "Number of hidden items: " + ((xlPIA.PivotItems)xlPF.get_HiddenItems(Type.Missing)).Count;
    }

Applying a Manual Inclusive Filter Using Visual C#

A manual inclusive filter is the default type of manual filter tracks the included items so that if new items are added in the database, when you refresh the PivotTable, the filter still reflects the original items that you selected. This behavior is new in Excel 2007. If you want 2003 behavior (that is, you want the items in your filter to update when the database is updated), set the IncludeNewItemsInFilter property and the HiddenItemsList property as follows.

xlPF.IncludeNewItemsInFilter = True;

xlPF.HiddenItemsList = items;

xlPF is the PivotField object to apply filtering on, and items is the array of item names that will be included in the filter.

NoteNote

To access the IncludeNewItemsInFilter property in the user interface, right-click an item in the field, and then click Field Settings. In the Field Settings dialog box, select or clear the Include new items in manual filter check box.

The following code example shows how to apply a manual inclusive filter to a PivotField object in an OLAP PivotTable.

/// <param name="xlPF">PivotField to apply filtering on</param>
/// <param name="items">Array of item names that will be included in filter</param>
public static void OlapManualFilter(xlPIA.PivotField xlPF, string[] items, xlPIA.Worksheet outputSheet)
{
      xlPF.VisibleItemsList = items; 

If you want to simulate the 2003 behavior, set xlPF.IncludeNewItemsInFilter = True, and set xlPF.HiddenItemsList = Items (items that you want to filter out). To access this property in the user interface, right-click an item in the Field, click Field Settings, select or clear the Include new items in manual filter check box.

outputSheet.get_Range("A1", Type.Missing).Value2 = "Manual filter info for the following PivotField: " + xlPF.Name;

outputSheet.get_Range("A2", Type.Missing).Value2 = "Number of items that are visible: " + ((Array)xlPF.VisibleItemsList).Length;

Unlike a relational PivotTable, an OLAP PivotTable cannot use the HiddenItemsList and VisibleItemsListBoolean values in the same mode. The HiddenItemsList works only when xlPF.IncludeNewItemsInFilter = False and the VisibleItemsList works only when xlPF.IncludeNewItemsInFilter = True

When a field is in the Report Filter area (Page Area), you cannot set xlPF.IncludeNewItemsInFilter equal to False. Therefore, with OLAP Report Filter fields, you must use the VisibleItemsList collection.

This article describes how to apply various types of filters to PivotTable data. Filters enable you to display only the data that you are interested in. This should reduce the complexity and give the PivotTable a clean, crisp appearance and behavior.

For more information about filtering and Microsoft Office Excel 2007, see the following resources:

Show:
© 2014 Microsoft