Excel 2010 Pivot Table Change data source

mpdillon

New Member
Joined
Feb 10, 2021
Messages
7
Office Version
  1. 2010
Platform
  1. Windows
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

New Member
Joined
Feb 10, 2021
Messages
7
Office Version
  1. 2010
Platform
  1. Windows
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

Image1.png


YES

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

Image2.png


Then clicked ADD. Selected the new MS SQL View



Image3.png




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


Image4.png



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

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
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

New Member
Joined
Feb 10, 2021
Messages
7
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,144,391
Messages
5,724,075
Members
422,535
Latest member
navjeet

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top