Linker IT Software
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
How to attach query results to a pivot table (Read 4018 times)
Gerrit-Jan Linker
YaBB Administrator

Posts: 75
How to attach query results to a pivot table
15.11.06 at 15:45:07
How to attach query results to a pivot table
The attached file shows how to attach query results to a pivot table.
This technique is very convenient if you want to do analysis while not being connected to the database.
When you requery, the new data will be attached to the existing pivot table.
To use the pivot table output target for select statements just execute the select statement from the SQL dialog. When you see the resultset dialog select the pivot table output target. Select a cell where you want the pivot table to be created and click OK.
SQL*XL will retrieve the data from the database and create a new pivot table. It should also display the pivot table toolbar that you can use the configure your pivot table.
Now you need to drag the fields you wish to use into the pivot table.
For Microsof's tutorial for pivot tables please look here: w_to_use_pivottables_excels_data_tool_du_jour.mspx
You can now select a pivot table as output target for your select statement. This can be very useful if you plan to do further investigations with your recordset. The pivot table keeps working even after you disconnect from the database. It therefore also provides an way to offline data from your database but without loosing the ability to reorder the data. Another good thing about pivot tables is that there is no 65,000 row limit. You can attach as many rows as you like. The data will be stored inside your xls file.
To refresh the pivot table SQL*XL uses the usual comment strategy. In the top left cell of the pivot table the SQL is stored. When you refresh the queries in a workbook SQL*XL also remembers which queries were dumped in Excel and which queries were created as pivot tables. You can keep using Ok for all on the Resultset Options Dialog to quickly refresh all queries.
See also:
New output target: Pivot Tables:  
SQL*XL to Pivot Table
Back to top
« Last Edit: 16.11.06 at 07:29:07 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1