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:

see Blank Pivot Table and Chart in Excel 2010

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.

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?

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.