Excel Office Script: Filtering Pivot Table based on External Data

Sven Aelterman 36 Reputation points Microsoft Employee
2025-01-29T23:52:15.01+00:00

Hi,

I have followed the code sample for working with Pivot Tables found here: https://learn.microsoft.com/en-us/office/dev/scripts/develop/pivottables

This code works and I have modified it further to create a filter hierarchy on the pivot table and select items "manually" using a PivotManualFilter object.

I am now trying to apply these concepts to my own Excel workbook which contains an existing pivot table. However, that table draws its data not from the Excel workbook but from an external connection (specifically, an Analysis Services OLAP cube).

I have written code that executes without errors, however, the filter is not applied. Instead, all values are shown.

If I first filter by the same field myself, that "manual" filter is removed but the manual filter I am applying instead is not effective (field.getFilters().manualFilter is null). Thus, all values are shown again after executing my code.

I think the behavior is due to an OLAP query that needs to be run in order for Excel to find the available values. When using the pivot table GUI, this is very obvious as there is a lengthy delay while the OLAP query executes and a message indicating this activity is shown on the status bar.

However, when applying the manual filter via Office Script, there is no such delay or activity shown.

How can I specify a manual filter to be used against a field from an external data source?

Thanks,

SA.

Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
2,152 questions
Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
4,249 questions
0 comments No comments
{count} votes

Accepted answer
  1. Segura, Jaime 85 Reputation points
    2025-02-03T18:28:38.7333333+00:00

    I use the same scripts with no result on my pivot table. Later I realized that as my Pivot Table was based from Power Pivot, all field, hierarchies are named different.

    When I debugged in order to get the proper syntax (also used VBA to record a macro to see the right way to define the elements in the manual filter array), it started to work. Unfortunately the office script only works only when triggered on Desktop app (I don't know why). The whole idea was to be used on a shared web version, but I think for the moment that option isn't yet available.

    2 people found this answer helpful.

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.