Pivot table accessing external data

[Guest]

Hello. In Excel 2003 I used to be able to create a pivot table that used a
query to access certain data in an SQL database. In 2007 I am not finding
how to create such a pivot table. I find I can connect to a database or a
table in a database but do not see how to draw from that table using a query.
Am I missing something?

Thanks,

 

[Guest]

I don't have Excel 2007 but did you check under the menu path the options for:
Data / Pivot table
Data / Import External Data
???

 

[Guest]

Thanks for the response William. The menu path is totally different for
2007. I am able to do what I want in 2003 but don't find a path to use
queries in 2007. The path offered will link to an external table but it
pulls down the entire table, not just the portion of the data garnered by the
query. When working with a remote database that can be a real bother.

JB

 

[Guest]

See here: http://blogs.msdn.com/excel/archive/2005/12/29.aspx

Also see here (scroll to the pivot table section):
http://contextures.com/xlExcel12Info01.html

Dave

 

[Guest]

Thanks Dave. There's alot of interesting stuff to delve into here, but most
of everything I see seems to assume that one is using the SQL Analytical
Services. What if I want to connect to a database table (or group of tables)
without having to pull all of this info down? Can I still use a query to
interface with the tables from Excel and go directly to a pivot table?

Thanks,
JB

 

[Guest]

Well, I'm sitting in front of XL 2007 right now, and here's what I see.

1) Go to the Insert tab
2) Select the pivot table icon on the left hand side of the ribbon
3) Choose the "Use an external data source" radio button
4) I don't use a SQL database as my external data source but rather an
Access database. To do this, I (a) navigate to the database on the local or
network drive, (b) "open" the data source/connection, (c) choose the
table/query, (d) create my pivot table.

So I guess the first question to ask you is: where in that sequence of steps
do you have a problem?

Dave

 

[Guest]

Hey Dave. I have to recant. In the docs you provided links to I did find my
answer (in a roundabout way). But first, to answer your question below -
using the series of steps you note with SQL tables allows you to select a
table but it pulls down the whole table and you control what you look at by
using the filters, etc. You can't select a query in that process, at least
as far as I could see.

What I did find was that if I begin the query process from a spreadsheet (no
mention of pivot tables yet....), connect to the outside data source, build
my query as normal, and when the usual dialog box opens to ask what cell I
want the data returned to there is also a radio button there allowing me to
return the data in pivot table format. I select the pivot table option and
'voila', I got what I wanted.

Thanks for the lead that got me there.

Jim B