Articles

Articles

Articles

Advanced features:

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 may 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:

Budget Diagram

A database developer can create a stored procedure that selects data into an Excel table and configure saving changes back to a database.

Data table

Note that stored procedure parameters (DepartmentCode, ItemCode, and Date) are shown on the ribbon.

Users can create a pivot table like this:

Pivot table Items * Departments

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 may place required parameters into page fields.

Pay attention that the ribbon shows the same controls as for the underlying table.
You may change parameters, reload data, and save changes.

When you edit the value in the pivot table, the SaveToDB add-in makes the following:

  1. The add-in filters the underlying table using pivot table values on the axes and page fields.
  2. 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.
  3. The add-in refreshes the pivot table.

You may see the filtered rows after each change.

Also, you may 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:

Filtered data

The following screenshots show different pivot table layouts for editing data.

Pay attention that you may change query parameters using the ribbon, and save and restore layouts using the Views group controls.

Pivot table Items * Dates
Pivot table Departments * Dates

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.

 

Working with Cursors and Fields

The SaveToDB add-in allows updating cells with active table row values and changing the active table row values using the cells.

Such cells should have special names like field_<table name>_<field name>.

You may define names manually using the Define Name button in the Formulas menu. Omit spaces in field names.

Also, you may use the Add Form Fields item in the Form Wizard menu to insert entire field set at once.

Then you may delete unused cells. Use the Clean Named Cells item in the Form Wizard menu to remove names for deleted cells.

To highlight the active table row, you may add a cursor using the Add Cursor item in the Form Wizard menu.

Cursor rows can be changed from VBA macros using the SaveToDB methods like MoveNext, MovePrevious, MoveFirst, and MoveLast.

Cells can have multiple names. This feature can be used to create master-detail tables.

The idea is simple. A cell must have two names:

  • field_<master table name>_<field name>
  • <detail table parameter name>

So when a user selects a row in the master table, SaveToDB updates the cell with the field value.

Then SaveToDB updates the query on the cell change event as the parameter value has been changed.

See Query Parameter Groups about using and defining parameters.

This technique can be used several times on a worksheet.

Here is an example of described features:

Example of using cursors and fields

The CustomerIndex table is a master for the Customers table.

The Customers table is a master for the Orders table.

The Orders table is a master for the OrderDetails table.

All the three tables have cursors.

The Customers and Order tables have hidden columns. The values of hidden columns are shown in the form fields.

You may see the selected cell with the field_Customers_City name.

You may find this example in the Northwind folder of examples for Microsoft SQL Server and Microsoft SQL Server Compact.

 

Working with Images

The SaveToDB add-in automatically shows images on task panes from database tables and views.

The add-in loads images when the row is selected.

You may dock task panes to the right or left sides, to the top or to the bottom.

If you close a task pane, you may activate it again using the Show Task Panes item in the Options submenu.
To show a closed task pane when there are several task panes, close all task panes and use the item.

Database developers can create selection change handlers to show images for any database query.

Just specify the _TaskPane value in the TARGET_WORKSHEET field and return the image as a handler result.

Task panes are worksheet related.

An example of showing photos from a database:

Example of loading photos from a database

You may find this example in the Northwind folder of examples for Microsoft SQL Server and Microsoft SQL Server Compact.

The SaveToDB add-in also shows images from the web if the table row has a URL with jpg, gif or png extensions.

For example, the following table contains a hidden column with photo URLs on LinkedIn like

https://media.licdn.com/media/p/7/000/253/05b/308dd6e.jpg:

Example of using photos from the web

You may find this example in the Web Images folder of the Examples for Web.

The SaveToDB add-in caches loaded images.

You may clean the cache using the Clean Image Cache Folder button on the Developer Options tab in the Options dialog box.

Working with Windows

The SaveToDB add-in adds to Microsoft Excel features for advanced working with windows:

  • Auto-activating related windows.
  • Auto-arranging related windows.

If the auto-activating is turned on in Options, new windows are "bound" to the first window sheet.

When another sheet is selected in the first window, the add-in saves current window configuration for the previous active sheet and loads related windows for the new active sheet as they were saved before.

Sheets in the first and related windows can have different window settings.

If the auto-arranging is turned on in Options, the windows are arranged when any window has been resized.

For example, you may move a window border or move the entire window.

In contrast, the Microsoft Excel auto-arranging stops its work when the window border is moved.

The example of arranged related windows:

Example of automatic arranging Excel windows

The windows 2 and 3 have been hidden when the Sheet2 is selected in the first window:

Example of automatic activating related Excel windows

If the Sheet1 is selected again, then the windows 1, 2, and 3 will be restored to the same size and locations.

Working with Auto-Filters

The first rows above tables can be used to change table auto-filters.

To change the auto-filter, just enter a value in the cell above the table column.

The following substitution characters are available: ?, *, >, <, <>.

For example, the '<>*AA*' value filters all the rows except the rows which contain 'AA'.

To turn off a current column auto-filter, just clear the value in the cell over the table column.

This feature can be turned on or off in the Options menu.

In the example on the screenshot, the rows with the sum above 100000 and the 'Inc' value in the company names are filtered.

Example of using auto-filters

Working with Calendar

The SaveToDB Calendar allows inserting dates into Excel cells.

Double-click on a date cell to launch the calendar.

Example of the SaveToDB calendar form

Differently from other calendar add-ins, the SaveToDB calendar supports Excel Undo.

This feature can be turned on or off in the Options menu.

Working with Foreign Keys

Starting SaveToDB 6.5, you may implement complex Excel forms based on tables, views, and stored procedures with fewer efforts as the add-in solves all tasks of converting between foreign key values and their names.

For example, a database contains the following tables and relations:

Example database schema: tables and relations

The dbo38.Payment table contains foreign keys from the dbo38.Account, dbo38.Company, and dbo38.Item tables.

To create a form for the dbo38.Payment table, we just add the ValidationList handlers to the dbo01.EventHandlers table as shown below:

Validation list configuration

As a result, we have the following table in Excel:

Excel table with validation lists

The add-in has replaced the integer key values to names in Excel and has added validation lists that allow choosing values from the tables of foreign keys.

You may apply this technique also to views and stored procedures. See examples in the Developer Guide 6.5 for SQL Server in the SaveToDB SDK.

When a user selects a cell with a validation list, the add-in shows the List Editor like this:

List editor

The List Editor allows selecting values more conveniently. Just double-click on a value or press Enter on a selected value.

You may also filter values. Just type several characters from the desired name.

If you have closed the editor, you may reopen it using Options, Show List Editor Task Pane.

Working with DDE Formulas

Database queries can return different formulas for different rows that can be used to configure DDE and RTD (Real-Time Data) formulas.

Customizing DDE formulas using SQL is much easier than customizing using macros.

You may use Microsoft SQL Server Compact or SQLite database located in the workbook directory.

Below is a step-by-step guide.

For example, a workbook has a table with the Symbol and Comment fields. We have to add DDE formulas for the Thinkorswim trading platform (TOS).

Simple steps to create an application:

  1. Create a database in the workbook directory and create the WatchList table using the Publish Wizard.
  2. Install SaveToDB Framework into the database using the Application Installer.
  3. Generate a configuration workbook using the Application Workbooks wizard.
  4. Add the source WatchList table and a new SQL code (WatchList TOS) with DDE formulas into the QueryList configuration table (see below). Save the table.
  5. Connect to the WatchList TOS query on a new sheet using the Connection Wizard.

As a result, we have an Excel table with configured DDE formulas.

You may add, delete, and update data (Symbol and Comment). Then click the Save button.

When the add-in saves the table, it refreshes the data and updates formulas for new symbols.

Below is an example of the QueryList table configuration::

IDTABLE_SCHEMATABLE_NAMETABLE_TYPEINSERT_PROCEDUREUPDATE_PROCEDUREDELETE_PROCEDURE
1 WatchListTABLE   
2SELECT
Symbol
, '=TOS|LAST!' + Symbol AS Last
, '=TOS|NET_CHANGE!' + Symbol As NetChange
, '=TOS|PERCENT_CHANGE!' + Symbol As Change
, '=TOS|HIGH!' + Symbol As High
, '=TOS|LOW!' + Symbol As Low
, '=SUBSTITUTE(TOS|VOLUME!' + Symbol+'," ","")+0' As Volume
, Comment
FROM
WatchList
WatchList TOSCODEWatchListWatchListWatchList

The table contains the SQL code in the TABLE_SCHEMA column and the CODE type in the TABLE_TYPE field.

The INSERT_PROCEDURE, UPDATE_PROCEDURE, and DELETE_PROCEDURE fields contain the WatchList table as a target table to save changes.

See Configuring Query Lists and Configuring Saving Changes.

Possible further steps to modify the application:

  • You may change WatchList columns using the Publish Wizard. Just republish the table.
  • You may change existing queries in the QueryList table in the configuration workbook. Then reload data and configuration in the working workbook.
  • You may add new queries to the QueryList table in the configuration workbook and open them in the working workbook using the Connection Wizard or the query list.
This website is using cookies. By continuing to browse, you give us your consent to our use of cookies as explained in our Cookie Policy.OK