Intro to the Advanced Setup
The Advanced Setup is in retirement state. It will be available in the application and we continue to support it, but there will be new features implemented in the SQL Spreads Designer that will not be available from the the Advanced Setup.
We always recommend that you use the SQL Spreads Designer as a first choice and only switch over to the Advanced Setup if necessary. If you are uncertain how to implement your solution in the SQL Spreads Designer, please reach out to our support team for guidance.
There are two ways in SQL Spreads to connect a spreadsheet to SQL Server so that the data in the SQL Server tables can be Updated, Inserted and Deleted from Excel; the SQL Spreads Designer and the Advanced Setup.
The SQL Spreads Designer
The SQL Spreads Designer is an easy way to select a table in SQL Server and configure how the table is updated from Excel:
Read more about how to use the SQL Spreads Designer here.
The Advanced Setup
The Advanced Setup menu lets you create a Database Import to fetch data from SQL Server and a Database Mapping to write data back to SQL Server.
To see the Advanced Setup menu, open Document Settings and check the Show Advanced Setup menu checkbox:
When do I have to use the Advanced Setup?
In most cases, you can use the SQL Spreads Designer, but there are a few features that are only available through the Advanced Setup. Those features are:
- Formula columns - columns in Excel containing formulas where the value of the formula is written to the database table.
Can I switch from the Designer to the Advanced Setup?
Yes, you can always start creating a setup in the Designer and then switch over to the Advanced Setup and continue configuring your setup there.
The easiest way to get started with the Advanced Setup is to first select the table in the SQL Spreads Designer. The Designer will create one Database Import and one Database mapping that you can go in and adjust using the Advanced Setup. Once you have edited a setup in the Advanced mode you cannot go back and do changes using the SQL Spreads Designer.
Once you have edited a setup in the Advanced mode you cannot go back and do changes using the SQL Spreads Designer.
Can I really do this from the Designer?
You can do most of the setups using the SQL Spreads Designer. If you need some guidance, please reach out to our support team for further help.
Can I use custom SQL queries or queries with JOINs from the Designer?
Yes, the SQL Spreads Designer supports updating data in SQL Server Views. If you need to use a custom SQL query in the SQL Spreads Designer, consider creating a SQL Server View which contains the custom SQL query and then use that View from within the SQL Spreads Designer.
Can I use the Tree filters from within the Designer?
Yes, please see this article for how to add a Tree filter in the SQL Spreads Designer.
Are there any other differences between the Advanced Setup and the SQL Spreads Designer?
There are a few differences:
- The SQL Spreads Designer connects one SQL Server table per Excel worksheet. You can have multiple SQL Server tables in one Excel document, but you will have to put each table in a separate worksheet. In the Advanced Setup you can have several SQL Server tables in one Excel worksheet.
- When you refresh or save the data in a worksheet connected to SQL Server using the SQL Spreads Designer, only the data in that particular worksheet will be refreshed/saved to the database. Whenever you refresh or save to the database in an Excel document, all connections that are created using the Advanced Setup will refresh and save.