Pivot Table Enhancements
The SaveToDB add-in enhances pivot table features. With it, you can:
- Use pivot tables as editors
- Connect pivot tables to stored procedures
- Save and restore pivot table views
- Change underlying query parameters using the ribbon
- Filter underlying rows instead of creating new tables
Pivot Tables as Editors
Consider the following table:
| employee_id | territory_id | sales |
|---|---|---|
| Employee 1 | Territory 1 | 100 |
| Employee 1 | Territory 2 | 200 |
| Employee 2 | Territory 3 | 300 |
You can create a pivot table using this Excel data table as the data source. Place the employee_id and territory_id columns on the axes, and use the SUM of the sales column for the cells.
The resulting pivot table will look like this:
| Territory 1 | Territory 2 | Territory 3 | |
|---|---|---|---|
| Employee 1 | 100 | 200 | |
| Employee 2 | 300 |
You can now edit values directly in the pivot table, and the add-in will update the underlying table values accordingly.
If the table is not editable, try cleaning old items using the Clean Pivot Items option in the Developer Tools menu.
Keep in mind that each pivot table cell must correspond to a single row in the underlying table to be editable. If the underlying object allows saving changes, click the Save button on the pivot table worksheet.
If the editable table column includes validation lists, the add-in will also add those list elements to the pivot table axes.
Connecting Pivot Tables to Stored Procedures
You can connect pivot tables to stored procedures using the Pivot Table Connection Wizard. Additionally, you can modify procedure parameters using the ribbon controls.
Pivot Table Views
Use the Views group controls to save and restore pivot table views. You can also save these views in a database to share them with your colleagues, similar to table views.
Ribbon Query Parameters
When using a connected table as a pivot table data source, the add-in displays the underlying query parameters in the ribbon. This allows you to change query parameters directly from the pivot table worksheet.
Filter Underlying Rows
Double-clicking a pivot table cell in Excel typically creates a new table with the underlying rows. However, the add-in allows you to filter the underlying rows in the source table instead. You can enable or disable this feature using the Options dialog box.