Have you ever distributed reports containing PivotTables and found that your recipients aren’t able to interact with them, only having a snapshot of the data? Wouldn’t it be great if they could have some of the same functionality you do, like the ability to filter, drill down, or add other fields that may be of interest?
Typically, when a recipient receives a workbook like this, opens it and tries to work with the PivotTable, they receive a message saying that the Table was saved without the underlying data, and needs to be refreshed in order to be updated.
Then, after clicking the Refresh button on the Data tab, they’re told that the PivotTable source file can’t be opened.
Getting around this is easy. All you have to do is save the underlying data with the PivotTable, which is an option in its properties.
1.Before distributing your report, right click on your Table and select PivotTable Options.
2. Then, under the Data tab, check the box saying Save source data with file, and then click OK.
3. You can now go ahead and distribute your report. Once a recipient receives it and opens it, they’ll first need to enable the content.
4. They’ll then receive a message saying that some of the workbook links can’t be updated. After clicking Continue they’ll be able to interact with the PivotTable.
One thing to note is that by saving the source data with the PivotTable, the size of the distributed file will increase. This is something to keep in mind if working with really large data sets, or if using email distribution and file size limits are imposed by the service providers.
