Tutorial: Working with Pivot Filters in Aspose.Cells Cloud

Prerequisites

Before starting this tutorial, make sure you have:

An Excel file with an existing pivot table
Your Aspose Cloud API credentials (Client ID and Client Secret)

Introduction

Pivot table filters allow you to focus on specific segments of your data, making it easier to analyze and draw conclusions. Filters can be applied to row fields, column fields, or as page fields (report filters) that affect the entire pivot table.

In this tutorial, we’ll learn how to work with pivot filters using the Aspose.Cells Cloud API.

Tutorial Steps

Step 1: Authenticate with the API

First, obtain your access token:

curl -v "https://api.aspose.cloud/connect/token" \
-X POST \
-d "grant_type=client_credentials&client_id=YOUR_CLIENT_ID&client_secret=YOUR_CLIENT_SECRET" \
-H "Content-Type: application/x-www-form-urlencoded" \
-H "Accept: application/json"

Save the access token from the response for use in subsequent API calls.

Step 2: Understanding Filter Types

Before adding filters, it’s important to understand the different types of pivot filters available:

Value Filters: Filter based on the values in a data field
- Examples: Top 10 items, values greater than a specific amount, etc.
Label Filters: Filter based on the text or labels in row/column fields
- Examples: Labels containing specific text, labels that begin with certain characters, etc.
Date Filters: Specific filters for date fields
- Examples: This month, last quarter, year to date, etc.
Custom Filters: Advanced filters combining multiple conditions
- Examples: Sales between $1,000 and $5,000, products with names not containing “old model”, etc.

Step 3: Adding a Basic Pivot Filter

To add a filter to a pivot table, use the PUT endpoint:

PUT https://api.aspose.cloud/v3.0/cells/{name}/worksheets/{sheetName}/pivottables/{pivotTableIndex}/PivotFilters

Here’s an example of adding a simple filter:

curl -v "https://api.aspose.cloud/v3.0/cells/Sales_Data.xlsx/worksheets/Sheet2/pivottables/0/PivotFilters?needReCalculate=true" \
-X PUT \
-d '{
  "FieldIndex": 1,
  "FilterType": "Count",
  "MeasureFldIndex": 3
}' \
-H "Content-Type: application/json" \
-H "Accept: application/json" \
-H "Authorization: Bearer YOUR_ACCESS_TOKEN"

This adds a filter that shows only items with a count of values in the field at index 3.

Key parameters:

FieldIndex: The 0-based index of the field to filter (1 in this example)
FilterType: The type of filter to apply (“Count” in this example)
MeasureFldIndex: The index of the measure field to use (3 in this example)
needReCalculate: Set to true to recalculate the pivot table after applying the filter

Step 4: Adding a Value Filter

Value filters are useful for focusing on items that meet specific criteria based on their numerical values. Here’s an example of adding a “Top 10” filter:

curl -v "https://api.aspose.cloud/v3.0/cells/Sales_Data.xlsx/worksheets/Sheet2/pivottables/0/PivotFilters?needReCalculate=true" \
-X PUT \
-d '{
  "FieldIndex": 0,
  "FilterType": "Top10",
  "Value1": "10",
  "MeasureFldIndex": 3
}' \
-H "Content-Type: application/json" \
-H "Accept: application/json" \
-H "Authorization: Bearer YOUR_ACCESS_TOKEN"

This filter shows only the top 10 items from field index 0, based on the values in field index 3.

For a “Bottom 10” filter, you would modify the request body:

{
  "FieldIndex": 0,
  "FilterType": "Top10",
  "Value1": "10",
  "Value2": "False",  // False for bottom items
  "MeasureFldIndex": 3
}

Step 5: Adding a Label Filter

Label filters allow you to filter items based on their text values. Here’s an example of a label filter that shows only items with labels containing “East”:

curl -v "https://api.aspose.cloud/v3.0/cells/Sales_Data.xlsx/worksheets/Sheet2/pivottables/0/PivotFilters?needReCalculate=true" \
-X PUT \
-d '{
  "FieldIndex": 1,
  "FilterType": "CaptionContains",
  "Value1": "East"
}' \
-H "Content-Type: application/json" \
-H "Accept: application/json" \
-H "Authorization: Bearer YOUR_ACCESS_TOKEN"

This filter shows only items from field index 1 that have “East” in their labels.

Step 6: Adding a Date Filter

For date fields, you can apply specialized date filters. Here’s an example of a date filter that shows only data from a specific quarter:

curl -v "https://api.aspose.cloud/v3.0/cells/Sales_Data.xlsx/worksheets/Sheet2/pivottables/0/PivotFilters?needReCalculate=true" \
-X PUT \
-d '{
  "FieldIndex": 2,
  "FilterType": "DatePeriod",
  "Value1": "Q1"
}' \
-H "Content-Type: application/json" \
-H "Accept: application/json" \
-H "Authorization: Bearer YOUR_ACCESS_TOKEN"

This filter shows only data from the first quarter (Q1) in field index 2.

Step 7: Retrieving Filters

To check what filters are currently applied to a pivot table, use the GET endpoint:

curl -v "https://api.aspose.cloud/v3.0/cells/Sales_Data.xlsx/worksheets/Sheet2/pivottables/0/PivotFilters" \
-X GET \
-H "Content-Type: application/json" \
-H "Accept: application/json" \
-H "Authorization: Bearer YOUR_ACCESS_TOKEN"

This returns information about all filters applied to the pivot table.

Step 8: Retrieving a Specific Filter

To get details about a specific filter, use its index:

curl -v "https://api.aspose.cloud/v3.0/cells/Sales_Data.xlsx/worksheets/Sheet2/pivottables/0/PivotFilters/0" \
-X GET \
-H "Content-Type: application/json" \
-H "Accept: application/json" \
-H "Authorization: Bearer YOUR_ACCESS_TOKEN"

This returns detailed information about the first filter (index 0) in the pivot table.

Step 9: Removing a Specific Filter

To remove a specific filter, use the DELETE endpoint with the filter index:

curl -v "https://api.aspose.cloud/v3.0/cells/Sales_Data.xlsx/worksheets/Sheet2/pivottables/0/PivotFilters/0" \
-X DELETE \
-H "Content-Type: application/json" \
-H "Accept: application/json" \
-H "Authorization: Bearer YOUR_ACCESS_TOKEN"

This removes the filter at index 0 from the pivot table.

Step 10: Removing All Filters

To remove all filters from a pivot table, use the DELETE endpoint without specifying a filter index:

curl -v "https://api.aspose.cloud/v3.0/cells/Sales_Data.xlsx/worksheets/Sheet2/pivottables/0/PivotFilters" \
-X DELETE \
-H "Content-Type: application/json" \
-H "Accept: application/json" \
-H "Authorization: Bearer YOUR_ACCESS_TOKEN"

This removes all filters from the pivot table.

Try It Yourself

Now it’s your turn to practice working with pivot filters:

Create a new Excel file with a simple pivot table (or use one from a previous tutorial)
Add a value filter (such as a Top 10 filter)
Add a label filter to show only specific categories
Retrieve information about your filters
Remove one of the filters
Remove all filters

Code Examples

Python Example

import requests
import json

# Authentication
auth_url = "https://api.aspose.cloud/connect/token"
auth_data = {
    "grant_type": "client_credentials",
    "client_id": "YOUR_CLIENT_ID",
    "client_secret": "YOUR_CLIENT_SECRET"
}
auth_headers = {
    "Content-Type": "application/x-www-form-urlencoded",
    "Accept": "application/json"
}

auth_response = requests.post(auth_url, data=auth_data, headers=auth_headers)
access_token = auth_response.json().get("access_token")

# API headers
headers = {
    "Content-Type": "application/json",
    "Accept": "application/json",
    "Authorization": f"Bearer {access_token}"
}

file_name = "Sales_Data.xlsx"
sheet_name = "Sheet2"
pivot_index = 0
base_url = f"https://api.aspose.cloud/v3.0/cells/{file_name}/worksheets/{sheet_name}/pivottables/{pivot_index}"

# Add a Top 10 filter
filters_url = f"{base_url}/PivotFilters?needReCalculate=true"
top10_filter = {
    "FieldIndex": 0,
    "FilterType": "Top10",
    "Value1": "10",
    "MeasureFldIndex": 3
}
response = requests.put(filters_url, data=json.dumps(top10_filter), headers=headers)
print("Add Top 10 Filter Response:")
print(response.json())

# Add a label filter
label_filter = {
    "FieldIndex": 1,
    "FilterType": "CaptionContains",
    "Value1": "East"
}
response = requests.put(filters_url, data=json.dumps(label_filter), headers=headers)
print("\nAdd Label Filter Response:")
print(response.json())

# Get all filters
response = requests.get(filters_url, headers=headers)
print("\nGet All Filters Response:")
print(response.json())

# Get a specific filter
specific_filter_url = f"{base_url}/PivotFilters/0"
response = requests.get(specific_filter_url, headers=headers)
print("\nGet Specific Filter Response:")
print(response.json())

# Remove a specific filter
response = requests.delete(specific_filter_url, headers=headers)
print("\nRemove Specific Filter Response:")
print(response.json())

# Remove all filters
response = requests.delete(filters_url, headers=headers)
print("\nRemove All Filters Response:")
print(response.json())

C# Example

using System;
using System.Net.Http;
using System.Text;
using System.Threading.Tasks;
using Newtonsoft.Json;

class Program
{
    static async Task Main()
    {
        // Authentication
        var client = new HttpClient();
        var authContent = new StringContent(
            "grant_type=client_credentials&client_id=YOUR_CLIENT_ID&client_secret=YOUR_CLIENT_SECRET",
            Encoding.UTF8,
            "application/x-www-form-urlencoded");
        
        var authResponse = await client.PostAsync("https://api.aspose.cloud/connect/token", authContent);
        var authResult = JsonConvert.DeserializeObject<dynamic>(await authResponse.Content.ReadAsStringAsync());
        string accessToken = authResult.access_token;
        
        // Set up API request headers
        client.DefaultRequestHeaders.Add("Authorization", $"Bearer {accessToken}");
        client.DefaultRequestHeaders.Add("Accept", "application/json");
        
        string fileName = "Sales_Data.xlsx";
        string sheetName = "Sheet2";
        int pivotIndex = 0;
        string baseUrl = $"https://api.aspose.cloud/v3.0/cells/{fileName}/worksheets/{sheetName}/pivottables/{pivotIndex}";
        string filtersUrl = $"{baseUrl}/PivotFilters?needReCalculate=true";
        
        // Add a Top 10 filter
        var top10Filter = new
        {
            FieldIndex = 0,
            FilterType = "Top10",
            Value1 = "10",
            MeasureFldIndex = 3
        };
        
        var top10Content = new StringContent(
            JsonConvert.SerializeObject(top10Filter),
            Encoding.UTF8,
            "application/json");
        
        var response = await client.PutAsync(filtersUrl, top10Content);
        string result = await response.Content.ReadAsStringAsync();
        Console.WriteLine("Add Top 10 Filter Response:");
        Console.WriteLine(result);
        
        // Add a label filter
        var labelFilter = new
        {
            FieldIndex = 1,
            FilterType = "CaptionContains",
            Value1 = "East"
        };
        
        var labelContent = new StringContent(
            JsonConvert.SerializeObject(labelFilter),
            Encoding.UTF8,
            "application/json");
        
        response = await client.PutAsync(filtersUrl, labelContent);
        result = await response.Content.ReadAsStringAsync();
        Console.WriteLine("\nAdd Label Filter Response:");
        Console.WriteLine(result);
        
        // Get all filters
        response = await client.GetAsync(filtersUrl);
        result = await response.Content.ReadAsStringAsync();
        Console.WriteLine("\nGet All Filters Response:");
        Console.WriteLine(result);
        
        // Get a specific filter
        string specificFilterUrl = $"{baseUrl}/PivotFilters/0";
        response = await client.GetAsync(specificFilterUrl);
        result = await response.Content.ReadAsStringAsync();
        Console.WriteLine("\nGet Specific Filter Response:");
        Console.WriteLine(result);
        
        // Remove a specific filter
        response = await client.DeleteAsync(specificFilterUrl);
        result = await response.Content.ReadAsStringAsync();
        Console.WriteLine("\nRemove Specific Filter Response:");
        Console.WriteLine(result);
        
        // Remove all filters
        response = await client.DeleteAsync(filtersUrl);
        result = await response.Content.ReadAsStringAsync();
        Console.WriteLine("\nRemove All Filters Response:");
        Console.WriteLine(result);
    }
}

Common Filter Types and Their Uses

Here’s a quick reference for common filter types and when to use them:

Filter Type	Description	Use Case
Top10	Shows top or bottom N items	Find your best-selling products
CaptionEquals	Exact match for labels	Show data for a specific region
CaptionContains	Partial match for labels	Find products with “premium” in the name
CaptionGreaterThan	Labels alphabetically after a value	Companies starting with letters N-Z
DatePeriod	Date ranges like quarters, months	Compare quarterly sales performance
ValueEquals	Exact match for values	Find products with exactly $1,000 in sales
ValueGreaterThan	Values exceeding a threshold	Find high-value transactions (>$10,000)
ValueBetween	Values within a range	Analyze mid-range priced products
Count	Number of items	Filter by popularity (# of orders)

Best Practices for Using Pivot Filters

Start Broad, Then Narrow: Begin with minimal filtering, then add more specific filters as you focus your analysis
Combine Filter Types: Use both value filters and label filters together for more targeted analysis
Document Your Filters: Keep track of what filters you’ve applied, especially when sharing reports with others
Use Page Filters for Key Variables: Place important filtering fields (like Year, Region, or Product Category) as page filters for easy access
Avoid Over-Filtering: Too many filters can make your pivot table hard to understand and may hide important patterns

What You’ve Learned

In this tutorial, you’ve learned:

How to add different types of filters to your pivot tables
How to work with value filters, label filters, and date filters
How to retrieve information about existing filters
How to remove specific filters or all filters from a pivot table
Best practices for using filters effectively

Troubleshooting Tips

Filter Not Applied: Check that you’re using the correct field index and filter type
Unexpected Results: Verify that your Value1 and Value2 parameters are correctly formatted
Multiple Filters Issue: Remember that adding a new filter to the same field will replace any existing filter on that field
Empty Pivot Table: If a filter results in no matching data, your pivot table may appear empty