Creating/refreshing Excel pivot tablesw with data from SQL Server 2000

Question

Creating/refreshing Excel pivot tablesw with data from SQL Server 2000

Jason Marshall

SSCommitted

Points: 1791
More actions
June 26, 2006 at 3:06 pm

#97184

Good aftrnoon,
I am trying to create several pivot tables on an Excel spreadsheet that point to Stored Procedures in our SQL Server database. When I go through the "Get External Data" business (in Excel) in creating the pivot table, I am only getting a list of tables and views that are in the db. Question is, can Excel pivot tables even point at sprocs, or do i have to dump my sproc results into tables in order for Excel to 'see' them?
Thanks much,
Jason

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

Get External Data cannot see sprocs or results of sprocs. You will need to make a "pass through" query. See the Excel documentation for help on that.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

homebrew01 SSC Guru Points: 55548 More actions · Answer 2

All our pivot tables execute SQL Stored Procs. That way you don't have to do any code maint in the Pivot table, and if users want to 'Save As" their pivot tables to play around with them, they still get the current code from the centrally maintained SP in the database.

Also, we don't use DSNs in our Pivot Tables, because they often get "lost" when users get new machines, or servers get upgraded. We use UDLs, and put the connection code in the pivot table directly. Then it stays with the pivot table no matter who opens it.

For example:

OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=SQL_Database_Name_Here;Data Source=SQL_Server_Name_Here;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=;APP=PivotTableNameHERE;Use Encryption for Data=False;Tag with column collation when possible=False

Use the "Edit Query Table/Pivot Table" Add-In to maintain the UDL connection info and the SQL SP.

tcraig SSC Veteran Points: 253 More actions · Answer 3

In the choose datasource dialog, clear the Use Query Wizard checkbox. After you choose your datasource you will go directly to MSQuery. Click the SQL button and enter your sproc there.