The third type of data connector is the SQL connector. This connector allows you to access a SQL database and get the desired data using a SQL query. After adding a SQL connector, the data source management dialog will be opened.
You can edit the name of the data connector in the text box marked yellow. To generate a connection string to connect to your database, press the red marked 'Create' button. After establishing a connection to the database by generating a connection string, you can add an SQL query by pressing the green marked 'Create' button.
- Set up SQL connector - main dialog
Create Connection String
By pressing the red marked 'Create' button in the data source management dialog the connection properties dialog will be opened.
- SQL connector - Connection Properties dialog
The appearance of this dialog depends on the data source which can be chosen by pressing the 'Change' button marked red. In this screenshot, a 'Microsoft Access Database File' is chosen as data source. You can choose the database file by pressing the 'Browse' button marked green and set the user name and the password in the area marked yellow. But as explained before, the kind and number of properties will depend on the chosen data source. By pressing the orange marked button labeled 'Advanced...' the advanced property dialog will be opened. In this dialog you can edit the advanced options of your database connection. After configuring all relevant options, you can check if the database connection is working by pressing the purple marked button labeled 'Test Connection'.
By pressing the green marked 'Create' button in the data source management dialog the query design dialog will be opened.
- SQL Query Designer used to create SQL queries
You can add a column by double clicking an attribute of a table in your database. These attributes can be found in the area marked green. These attributes will be added to the area marked red, keeping the column headings shown in your data connector, as well as in the area marked blue, with the SQL query. Pressing the button marked yellow will add a 'GROUP BY' statement to the SQL query. Pressing the button marked orange will allow you to add advanced settings to your SQL query. Pressing the purple button will check SQL syntax. Pressing the light blue button will show you a preview of the data set retrieved by the SQL query. Pressing the button marked brown will reset the SQL query.