This is an advanced topic that may require some knowledge of databases.

Stonefield Query can report on any type of data source. To add a data source to the ones it already knows about, choose Other Data Sources from the Tools menu or the Data tab of the ribbon.

This function is only available if you have an Ultimate license and are an Administrator user.

This dialog shows a list of the other data sources you can report on and the tables in those data sources (in the case of a Microsoft Excel document, a table is a worksheet). To see the properties of a particular data source, such as the type of connection it uses, select it in the list. If you select a table, you can specify a descriptive name for the table (the name that appears, for example, in the Table list in step 2 of the report wizards) and the module the table belongs to.

To remove a data source or a table (which doesn't physically remove the data source or the table, just removes it from the list), select it and click Remove.

To add a data source, click the Add button to display the Data Source Wizard.

In order to do report on a data source, Stonefield Query needs to know the structure of the data in the data source. In the case of a database, that means what tables there are, what fields are in those tables, and how those tables relate to each other. For a Microsoft Excel spreadsheet, that means what worksheets are in the document and what columns are in each worksheet. The Data Source Wizard takes care of that.

Specify the connection settings for the data source in step 2.

There are two types of data sources you can specify: ODBC and Microsoft Excel.

ODBC

ODBC, or Open DataBase Connectivity, is a database access technology built into Windows that provides a consistent way of accessing data from different database engines. There are two ways to connect to an ODBC data source: using a DSN (data source name defined in the ODBC Data Source Administrator) or using a connection string.

If you have a DSN already set up, choose DSN and select the desired DSN from the Data source dropdown list. Enter the user name and password for the data source if required. Click the "show password" button toggle between displaying the password in plain text or as "*****" (the password is always stored encrypted regardless of the setting of this button).

If you do not have a DSN set up or would prefer to use a connection string, choose Connection string and enter the connection string to connect to the database. Connection strings can be tricky to get right, so click the Build button if you want some assistance. The Connection String Builder has the following settings:

• Driver: choose the ODBC driver to use from the dropdown list.

• Server: for server-based databases, such as Microsoft SQL Server, enter the name of the server.

• Database: for server-based databases, enter the name of the database to report on. For file-based databases, such as Microsoft Access, enter the name and path of the database file or click the "..." button to display a dialog in which you can locate and select the file.

• User name and password: enter the user name and password for the database if necessary. Click the "show password" button toggle between displaying the password in plain text or as "*****" (the password is always stored encrypted regardless of the setting of this button).

• Connection string: as you enter settings in this dialog, it automatically builds the connection string for you. However, you can edit it if necessary, such as adding other settings.

• Test: click this button to test that you can connect to the data source using the connection string.

Microsoft Excel

To report on the contents of a Microsoft Excel document, choose Microsoft Excel for the data source type and either enter the name and path for the document or click the "..." button to display a dialog in which you can locate and select the document.

Click Finish in step 3 to read the structure of your data source. Once it has finished, you are ready to report on it.