Working with Pivot Tables
The SaveToDB add-in adds the following features for working with pivot tables:
- The add-in allows editing values of pivot tables based on Excel tables.
- The add-in enables underlying table SaveToDB controls for pivot tables.
- The add-in allows saving and restoring pivot table views using the Views group controls.
- The add-in allows saving pivot table views in databases and loading views by end users from databases.
- The add-in allows changing default double-click behavior to filter underlying tables instead of creating ranges on new worksheets.
These features significantly reduce development time and costs as pivot tables can be used as edit forms.
Business analysts and end users can create pivot table edit forms themselves and save them into databases for use by other users.
Example of Using Pivot Tables as Edit Forms
For example, a database has the following tables:
A database developer can create a stored procedure that selects data into an Excel table and configure saving changes back to a database.
Note that stored procedure parameters (DepartmentCode, ItemCode, and Date) are shown on the ribbon.
Users can create a pivot table like this:
If each pivot table cells have only one row in the underlying table, then SaveToDB allows editing values directly in cells.
Tip: If the table is not editable, try to clean old items using the Clean Pivot Items item in the Pivot Wizard menu.
In the example, each cell in the table has unique ItemCode and DepartmentCode as they are on the axes.
The cells have unique Date value as the date is filtered using the Date parameter of the stored procedure.
You can place required parameters into page fields.
Pay attention that the ribbon shows the same controls as for the underlying table.
You can change parameters, reload data, and save changes.
When you edit the value in the pivot table, the SaveToDB add-in makes the following:
- The add-in filters the underlying table using pivot table values on the axes and page fields.
- If the filtered range contains a single row, the add-in changes the value.
If the filtered range has no rows, the add-in adds the row with values.
If the filtered range has more than one row, then the add-in does not change the table.
- The add-in refreshes the pivot table.
You can see the filtered rows after each change.
Also, you can double click on any cell to get an underlying range.
For example, double-click on the Direct Sales, SALES cell shows only the one row, and the cell is editable:
The following screenshots show different pivot table layouts for editing data.
Pay attention that you can change query parameters using the ribbon, and save and restore layouts using the Views group controls.
Pivot Table Views
Pivot table views can be stored in databases using the Table Format Wizard.
The table views and pivot table views are stored in the same format object of the underlying query.
So do not change and save table formats and pivot table formats in different workbooks.