Excel 2010 Pivot Table Change data source

[mpdillon]

Let me preface this with I know very little about Pivot tables.

I want to move the data source for the Pivot table from a local System ODBC driver to a MS SQL View. I have created a view in MS Sql that is identical to the existing ODBC query. All the posts I found reference menu choices I do not have or cannot find. The existing ODBC query populates a sheet named "Data".

How do I change the Pivot Table's data source to use the MS SQL View? Then how do I have the MS SQL view populate the worksheet "Data" (again all field names are identical and in the same order (I think)).

Your assistance will be greatly appreciated.

Thanks,
pat

 

[GlennUK]

So, basically, your question is "... how do I have the MS SQL view populate the worksheet "Data"" ... as, obviously, you can generate your PivotTable from that sheet? Also, can you explain why you want to "move" the data source, and not just create a new PivotTable?

 

[mpdillon]

Glenn,
Thank you for responding. The answer to your first question is YES. I want to populate the worksheet Data using a MS SQL View. Currently, that sheet is populated with an ODBC source.

The answer to your second question is that I do not want to create a new Pivot table. Let me offer my thoughts as to why. I did not create this pivot table. It has been in use for many years. It has many worksheets. I would not know how to recreate it. If I recreate the worksheet, I believe it would require much more work to validate that I duplicated it correctly.

Let me know what else you would need to assist.
Thanks,
pat

 

[GlennUK]

OK. Can you explain what you've found, as mentioned ... quote: "I have created a view in MS Sql that is identical to the existing ODBC query. All the posts I found reference menu choices I do not have or cannot find." ... what menu choices or methods were recommended, and that you could not find? It may be that you just need pointing in the right direction.

 

[mpdillon]

In the attachments you will see the instructions I found for changing the data source. The other image shows the actual data tab. I cannot find the references mentioned in the articles.

How would I go about changing the data source for this worksheet?
Thanks,
pat

 

[GlennUK]

The instructions are for changing the data source of a PivotTable. And you are not in a PivotTable?

 

[mpdillon]

I do not know how to tell if it is a Pivot table. What I do know is that the one worksheet, Data is used to populate all the other worksheets and those other worksheets contain summarized information which can be filtered. Please see PivotTable.png.

I have also included the Connection screen. I can see where the query "Query from _Data_01_MSSQL" is used to populate worksheet DATA. But I cannot figure out how to Edit that connection. Right clicking or double clicking does not allow me to edit it. Nor does clicking on properties when this is highligted.

 

[GlennUK]

You don't edit where it is used ... you edit the connection.

 

[mpdillon]

Can you guide me through the screens needed to change the connection used to populate Worksheet "Data". I do not understand now to change the connection from Data_01_MSSQL to ZZExcelPivot table.
Thanks,
pat

 

[GlennUK]

Just delete the data and query from the Data sheet, and create a new query with the connection you want.