Excel Office Script: Filtering Pivot Table based on External Data

Question

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.