Articles

Articles

Articles

Advanced features:

Developer tips:

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.

Using Parameters

Customizing Query Parameters

The SaveToDB add-in automatically detects parameters of stored procedures, OData functions, and HTTP queries.

SaveToDB allows users to customize WHERE fields of tables and views in a visual mode.

Developers can define parameters for other types of queries using special insertions in query texts.

These query texts are used in:

Parameter insertion formats:

Query TypeType KeywordParameter FormatExamples
SQL code for Microsoft SQL Server,
Microsoft SQL Server Compact, and SQLite
CODE@Parameter[=DefaultValue]SELECT * FROM Sales.Contacts WHERE Name = @Name
SELECT * FROM Sales.Contacts WHERE Name = @Name=ABC
SQL code for Oracle Database,
IBM DB2, MySQL, MariaDB, NuoDB, and PostgreSQL
CODE:Parameter[=DefaultValue]SELECT * FROM SALES.CONTACTS WHERE NAME = :Name
SELECT * FROM SALES.CONTACTS WHERE NAME = :Name=ABC
HTTP queryHTTPStandard HTTP parametershttp://www.google.com/finance/historical?q=GOOG
HTTP queryHTTP{Parameter[=DefaultValue]}http://www.google.com/finance/historical?q={Symbol=GOOG}
https://www.google.com/search?as_q={Query}
text queryTEXT{Parameter[=DefaultValue]}{FileName};CodePage=65001
{FileName};CodePage={CodePage=65001}
Macro commandMACRO{Parameter[=DefaultValue]}SayHello {Name=World}
Sheet1.SayHello {FirstName}, {LastName}
Windows Shell or CMDCMD{Parameter[=DefaultValue]}{FileName}
notepad.exe {FileName}
dir {Mask=*.*}
cmd /c dir {Mask=*.*}
cmd /k dir *.*
mailto:{Email}&subject=Thanks for the connection&body=Hello {FirstName},%0A

Remark: if the default value is specified, the equal sign "=" must have no leading or trailing spaces.

Customizing Query Parameter Value Lists

Developers can customize parameter value lists. See Configuring parameter values.

Parameter Values of Executed Procedures

When the SaveToDB add-in executes procedures and other queries, it may assign parameter values from different sources (shown in the priority order from highest to lowest):

Value SourceScopeComment
A table column of the same nameAll typesSource object column names without translation are used.
A ribbon parameter of the same nameAll typesRibbon parameters are shown for queries inserted into Excel tables.
Ribbon parameters are being synchronized with Excel named cells. Parameters with predefined names (see below) are not shown on the ribbon.
An Excel named cell of the same nameAll typesExcel named cell value
An active query parameter of the same nameAll typesFor example, event handlers of EXEC dbo.uspCustomers @ManagerID=101 can use a value of the ManagerID parameter.
WorkbookName, workbook_nameAll typesActive workbook name without directory
WorkbookPath, workbook_pathAll typesActive workbook directory. The value is empty for new workbooks.
SheetName, sheet_nameAll typesActive sheet name
DataLanguage, data_languageAll typesData language code selected using SaveToDB Options
TableName, table_nameEvent handlersActive query object
EventName, event_nameEvent handlersEvent name as defined in the EVENT_NAME field of table 2
ColumnName, column_nameEvent handlersActive column name
CellValue, cell_valueEvent handlersActive cell value. Use text type parameters and convert values to required data types.
CellNumberValue, cell_number_valueEvent handlersActive cell number value. Use number type parameters. This value is NULL if the cell has a non-number value.
CellDateTimeValue, cell_datetime_valueEvent handlersActive cell datetime value. Use datetime type parameters. This value is NULL if the cell has a non-datetime value.
CellAddress, cell_addressEvent handlersActive cell address
CellFormula, cell_formulaEvent handlersActive cell formula in the Range.Formula format
ChangedCellCount, changed_cell_countEvent handlersNumber of changed cells
ChangedCellIndex, changed_cell_indexEvent handlersNumber of command in the group of change cell handler commands
TransactionID, transaction_idAll typesUnique GUID of a group of executed commands.
TransactionStartTime, transaction_start_timeAll typesUTC start time of executing a group of commands.
WindowsUserName, windows_user_nameAll typesUser's Windows login
SaveToDbVersion, savetodb_versionAll typesSaveToDB version
MergeDate, merge_dateData mergeData merge SQL generation date and time. The value allows detecting data present in the latest data set.

Using Named Cells

Updatable Named Cells

SaveToDB updates specially named cells during data refresh:

Named CellDescription
SaveToDB_ConnectionStringThe query connection string.
SaveToDB_CommandTextThe query command text.
SaveToDB_ObjectThe database object name as <schema>.<name> or a TABLE_NAME value for SQL-codes, HTTP queries, and text file queries from the query list configuration view.
SaveToDB_NameThe value of the TRANSLATED_NAME field from the object translation configuration view if exists; otherwise, the same as SaveToDB_Object.
SaveToDB_DescThe value of the TRANSLATED_DESC field from the object translation configuration view.
SaveToDB_CommentThe value of the TRANSLATED_COMMENT field from the object translation configuration view.
SaveToDB_ElapsedMillisecondsQuery execution time in milliseconds.
<Parameter>Query parameter value. Changing cell values changes parameter values also.
<Parameter>__nameQuery parameter value name (two underscores).

You may use these named cells to make report headers and descriptions or to debug current query properties.

To show extended descriptions of Excel forms, you may use HTTP links to online documentation using HTTP handlers in the Actions menu.

SaveToDB_Name, SaveToDB_Desc, and SaveToDB_Comment depend on a data language selected using the Options menu.

SaveToDB also updates the cells named as active query parameters. Such cells also can be used to update the query parameters. This is a dual-direction link.

To insert a named cell, use the Formulas, Define Name Excel menu.

It is the best practice to use named cells with the sheet scope as a workbook can contain multiple data sheets.

Using Excel Formulas

Customizing Excel formulas in Databases

Users can add formula columns to any Excel data table. Such columns exist in the user workbooks only.
Users can save table formats including formula columns in databases and restore them in other workbooks.

Developers can define Excel formulas (calculated within Excel) directly in views, stored procedures, or in SQL codes.

For example, a view can contain a column with the formula '=[@Price]*[@Qty]' to calculate the row sum in Microsoft Excel.

Such formula columns are shown to all users and do not require additional actions to distribute calculated columns.

Formulas must be in the international Microsoft Excel formula notation to use in any localized version of Microsoft Excel.
The main rules are using English names of formulas and commas as a parameter separator.

Formulas are loaded from a database as text values, and SaveToDB changes the text to formulas.
The columns can show formula texts, and the screen can blink in this phase.
To fix this issue, define an empty value for showing string values in the cell format (fourths place in the custom cell format).
For example, the custom cell format "0;-0;;" turns off showing zero and string values.

Customizing 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 easy then customizing using macros.

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

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 table has been saved, the query is refreshed, and the formulas are updated for new symbols.

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 SQL code is specified in the TABLE_SCHEMA column; TABLE_TYPE must have the CODE type for such queries.

The WatchList table is specified in the INSERT_PROCEDURE, UPDATE_PROCEDURE and DELETE_PROCEDURE fields. So, data changes of the query can be saved to the base WatchList table.

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 into 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