Pivot Table Enhancements

Pivot Table Enhancements

The SaveToDB add-in enhances pivot table features. You can:

  1. Use pivot tables as editors
  2. Connect pivot tables to stored procedures
  3. Save and restore pivot table views
  4. Change underlying query parameters using the ribbon
  5. Filter underlying rows instead of creating new tables

Pivot Tables as Editors

For example, you have a table:

Employee 1Territory 1100
Employee 1Territory 2200
Employee 2Territory 3300

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 1Territory 2Territory 3
Employee 1100200
Employee 2300

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.