Delete all data from pivot table connected to SQL Server, to reduce file size.

Question

I have a pivot table that connects to a SQL Server view. From time to time it would be handy if I could e-mail the empty "shell" of the pivot table to other people and they could refresh it from the database themselves. I've sorted out the SQL Server security and permissions and so on. I Just need a way of "emptying" out the pivot table so it has no data. This would achieve two things:

1. Ensuring that when I e-mail the pivot table, it contains no sensitive data, and can only be populated by someone with appropriate SQL Server permissions
2. Ensuring the file size is small enough to e-mail

I've thought about

• temporarily changing the view to say WHERE 2 = 1
• refreshing the pivot table, saving and closing the excel workbook
• changing the view back
• e-mailing the empty table.

but Murphy's Law says that sooner or later someone will SELECT from the view at the very instant I change it to WHERE 2 = 1 and chew my head off because it's not returning data (fair enough too). Is there a way I can delete all the data in the pivot table without resorting to the method above?

Cheers

Greg

Answer 1

It's OK! I worked it out. Go to the pivot table Options ribbon. Then click [options]. Then go to the [data] tab. Then uncheck [Save source data with file]. Then set [Number of items to retain per field] to [None]. Then save and close. Hey presto! The file was 48MB. Now it's a trifling 69KB. How good is that?

Cheers.

Answer 2

Great for you. Seems to be harder when you use PowerPivot. The [Save source data with file is grayed out. Any ideas?

BR /Magnus

Answer 3

Thank you for posting and providing the answer. This will help other community users when they search for this type of issue.