Error : This PivotTable report is invalid

Introduction

I was struggling with a problem with a PivotTable in Excel for hours. Although I can't exactly pinpoint the source of the problem, I would like to share this problem and a possible solution with you.

The Problem

The situation is that I have a workbook with multiple sheets and every sheets shows another view of the data, for instance from a product hierarchy and a customer hierarchy. Now, the client wanted to use slicers to select all the pivot tables and pivot charts on multiple sheets in the workbook. I have shown the situation below.

Now the real pain. The following error messages kept on coming everything I tried : "This PivotTable report is invalid. Try refreshing the Data...".

It seems a problem in Excel and not PowerPivot that is running in the back.

The solution

After trying to turn off the reportconnections (and not succeeding), I decided to remove the second tabsheet and start from scratch and right away the problems vanished.

As a side effect the other slicers performed a lot better now.

Update 04-06-2016 (NL) : Today I ran again into this error. Now this time deleted a hierarchy that was in the pivottable and put it it back again and it works again. May be the cause could be that I was editing the underlying query in PowerPivot .

Conclusion

Cross selecting (Rob Collie already used cross filtering in another context) with the slicers to multiple pivot tables can be a real pain. So don't make it too complex or you'll end in a swamp of errors in Excel.

Greetz,
Hennie