Pivot Table Enhancements
The SaveToDB add-in enhances pivot table features. 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
For example, you have a table:
|Employee 1||Territory 1||100|
|Employee 1||Territory 2||200|
|Employee 2||Territory 3||300|
You can create a pivot table using the Excel data table as a data source.
Place the employee_id and territory_id columns to axes and the SUM of the sales column to cells.
You will see a table like
|Territory 1||Territory 2||Territory 3|
Now you can edit values in the pivot table. The add-in will update the underlying table values accordingly.
If the table is not editable, try to clean old items using the Clean Pivot Items item of the Developer Tools menu.
Also, note that each pivot table cell must have only one row in the underlying table to be editable.
If the underlying object allows saving changes, you can click the Save button on the pivot table worksheet.
If the editable table column has validation lists, the add-in adds list elements to its axis also.
Connecting Pivot Tables to Stored Procedures
You can connect pivot tables to stored procedures using the Pivot Table Connection Wizard.
Moreover, you can change procedure parameters using the ribbon controls.
Pivot Table Views
You can use the Views group controls to save and restore pivot table views.
Moreover, you can save such views in a database to share them with your colleagues.
It is similar to table views.
Ribbon Query Parameters
When using a connected table as a pivot table data source, the add-in places the underlying query parameters to the ribbon.
So, you can change query parameters right from the pivot table worksheet.
Filter Underlying Rows
When you double-click on a pivot table cell, Excel creates a new table with the cell underlying rows.
The add-in allows filtering the underlying rows in the source table instead.
You can turn on or off this feature using the Options dialog box.