Fine tuning the document setup

Document Settings are settings that affect the whole SQL Spreads Excel document.

To open the Document Settings dialog, open Design mode and then click the Document Settings button in the SQL Spreads tab:

Import from database settings

Automatically refresh import data when the document is opened

By default, SQL Spreads will reload the data from the SQL Server table into Excel when the Excel document is opened. To turn off the refresh of data from SQL Server when the document is opened, uncheck the “Refresh import data when the document is opened”.

Automatically refresh import data after data is saved to the database

To automatically reload the data from the SQL Server table into Excel right after the updates in Excel are saved to SQL Server, check the “Refresh import data after data is saved to database”.

Automatically scroll to the top after data has been imported

When using a Tree Filter, the number of rows loaded from SQL Server may vary for different filter selections. Use this option to always scroll to the top of the spreadsheet when new data is loaded from the database.

Save to database settings

Automatically save changes to the database when the Excel file is saved

To automatically save updates to SQL Server whenever the Excel document is saved, check the “Save changes to database when Excel file is saved".

Automatically save the Excel file after changes are saved to the database

Check this option to automatically save the Excel file after updates are written to the SQL Server database.

Call Excel's 'Refresh All' after changes are saved to the database

Check this option to automatically refresh all Excel's data connections and Pivot tables when the updates have been saved to SQL Server.

This feature can be used to automatically refresh a Pivot table or graph when changes are done to the underlaying data in SQL Server.

Show "Confirm changes" dialog before saving tothe database

By default, SQL Spreads will show a confirmation dialog before updates are saved to the SQL Server database. You can turn off the confirmation dialog by unchecking this option.

Design mode settings

Design mode password

To protect your SQL Spreads Design, you can add a SQL Spreads Designer password that needs to be entered to be able to edit this document in Design mode.

Show Advanced Setup menu

From SQL Spreads 6.0 the Advanced Setup is hidden by default. To display the Advanced Setup, check this checkbox Show Advanced Setup menu.

Your Advanced Setup will now be displayed in the SQL Spreads ribbon.

Other document settings

Working with protected Excel Sheets

If you use the Excel feature to protect the Excel worksheet in the Review tab in Excel, you must also enter the worksheet protection password here in the Document Settings.

Identifier for NULL values

The NULL value identifier decides which text that is used in cells for NULL values.

Legacy encryption for document backward compatibility

If you do an upgrade from a SQL Spreads version before 7.4.1, by default, all documents will continue to use the legacy encryption. The Use legacy encryption for document backward compatibility setting will be enabled automatically to ensure that there is backward compatibility between documents.

To move to the latest encryption for your document, you can uncheck the option to Use legacy encryption for document backward compatibility. Once you click on OK, a confirmation dialog will be shown asking if the latest encryption should be used by default. You can click OK.

If you do a fresh installation of 7.4.1, without an upgrade, then your documents will automatically use the latest encryption and this legacy encryption setting will be unchecked.

Data post processing using a SQL query

SQL Spreads can execute a data post processing SQL query after the data is saved to the database. This method can be used to copy, clean or in other ways process the data when changes to the data is done from SQL Spreads.

Click the Edit Post-Save SQL Query button to see a basic SQL editor where you can enter and test run your post-processing SQL query.

See this article for examples of how to use the Data Post processing feature in SQL Spreads.