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
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

GlennUK

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

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

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

New Member
Joined
Feb 10, 2021
Messages
7
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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
 

Attachments

  • Data Tab.png
    Data Tab.png
    87 KB · Views: 2
  • Search.png
    Search.png
    101.2 KB · Views: 2

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
The instructions are for changing the data source of a PivotTable. And you are not in a PivotTable?
 

mpdillon

New Member
Joined
Feb 10, 2021
Messages
7
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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.
 

Attachments

  • PivotTable.png
    PivotTable.png
    21.6 KB · Views: 3
  • Connections Replace.png
    Connections Replace.png
    17.8 KB · Views: 3
  • Connectinos.png
    Connectinos.png
    14.1 KB · Views: 3

mpdillon

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

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
Just delete the data and query from the Data sheet, and create a new query with the connection you want.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,474
Messages
5,636,536
Members
416,922
Latest member
defectexpress

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