Pivot Tables and Charts Blank after Excel Opens and Refreshes Data Sources

Question

I have an Excel 2010 workbook that contains two SQL Connections. Both connections are configured to refresh when the workbook opens.

There are two data tables in my workbook, each with a connection to one of these connections.

I have 3 Pivot Tables and 3 Pivot Charts that are sourced from these two tables.

When I open my workbook, for a split second, I can see the tables and charts formatted as one might expect them to be, and as I last saved them.

After the data tables have refreshed, all three of the Pivot Tables and Charts go blank:

Also, when I click anywhere on any Pivot Table, the Field List appears to the right with a list of generic Columns, like:

[] Column1
[] Column2
[] Column3
[] etc.

If I right-click on the Pivot table and choose refresh, the Field List then updates with the actual column names shown at the top of each data table, from which that Pivot table is sourced...

But still no data, rows, or columns appear in the Pivot table.

What do I do to prevent this? My Pivot tables and charts should show updated data and results, not go blank.

Help, thoughts, comments always appreciated.

Answer 1

I was able to resolve the issue by going into the Connection Properties dialog box (on both connections), and on the Usage tab, un-checking the check box 'Remove data from the external data range before saving the workbook', which I had previously checked.

But I don't know WHY this fixed the issue.

Anyone care to explain?