Post reply

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

  • 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

  • June 26, 2006 at 8:40 pm

    #645960

    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)
    Intro to Tally Tables and Functions

  • June 26, 2006 at 9:22 pm

    #645962

    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.

  • June 28, 2006 at 10:22 am

    #646418

    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.

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

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