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

 

[mpdillon]

Thank you. I am getting close. I have removed the data. I have removed the old ODBC Connection. I have added the new MS SQL View Connection.

I do not know how to associate this new MS SQL View with the worksheet "DATA" so that worksheet is populated when the data is refreshed. In Excel 2010 where do I go to associate the Connection with the worksheet. I did not see any way to do this in Properties screen in the Connections dialog.

Thanks,
pat

YES

Next I clicked on Data tab and confirmed the old query was removed.

Then clicked ADD. Selected the new MS SQL View

When I click on See where connections are used, I get the message:

Where do I go to assign the new MS SQL View to populate the worksheet “DATA”?

 

[GlennUK]

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

No, you do not want to change the source for the PivotTable ...we've been over this already. The PivotTable is sourced from a sheet ... you want the query that populates that sheet to be replaced. Just do an experiment in a blank/new workbook and get the query to populate a sheet ... you HAVE TO complete that step before going any further. Be aware that you can populate PowerPivot directly from a query ... but you already mentioned you want the data from the query in a sheet because of certain formulas ... just in case anybody is reading this, and wondering why I didn't recommend that route.

 

[mpdillon]

OK. Thank you for your assistance. I have it working. I also created a PDF for my future reference showing all the screen shots. But I do not see a way to upload that here.
After I made a copy of the sheet, I performed theses steps.

1. Opened the existing worksheet. Clicked on the "Data" worksheet. In my example, the worksheet that contains the data is named DATA.
2. With the data worksheet active, I clicked on the Design tab (There is a yellow box above the menu choice "Design" called Table Tools.)
3. With the Design tab active, I copied the "Table Name" and saved it for later use. (Table Name is at the far Left of the menu area. The name itself is mostly hidden. It is a very narrow field on my screen but the name can still be highlighted and copied).
4. Delete all the data including column headers. The easiest way I found to do this was delete entire columns.
5. Click on the Data menu choice.
6. If all the data is gone, the Button for "From other sources" and "Existing Connections" will be active.
7. Add new connection. Answer all the questions. Lastly you will be asked what cell to start the data. For me it was $A$1
8. Completing the "Add New Connection" will populate the sheet with data.
9. Next I entered the Old table name in the Table Name text box (Design tab, far left). see step 3.
10. I then had to go to each Pivot table in this workbook and;
a. Make a Pivot table field active. I chose to use the first column name header.
b. This will make the purple text above the menu entitled "PivotTable Tools" visible.
c. Choose Options
d. Choose Change Data Source >> Change Data Source.
e. Enter the TABLE NAME of your data worksheet. This is the table name you saved in step 3 and pasted into the data worksheet table name in step 9.

After that, the changing of the data source for the Pivot table was successful.

 

[GlennUK]

Sounds good.