Problem:
There’s an excel 2010 pivot table that’s getting its data from TestServer. How do I change the data source so that the Pivot Table get’s it data from ProdServer?
Note: This is a common scenario among Business Intelligence Developers who might want to switch between different servers (Test/Prod) to for comparing data.
Solution:
1. Establish the connection to a NEW data source via Data Tab in Excel.
2. Select the Pivot Table whose connection needs to be changed.
3. From the Excel Toolbar. Go to Pivot Table Tools > Options > Change Data Source
4. Choose the NEW connection from here. (you can use the connection that you created in step #1)
5. Click OK and that should have successfully changed the data source for you. Please test the column names/values that might have been affected since you changed the data source.
That’s about it for this post. Your comments are very welcome!