When working with data connections to relational databases or other non-cube data sources, you get the option to select one or more tables from the database in the Views in Connection dialog. Here, you also have the option to create your own custom database query.
To set up a connection based on a custom query:
Create a new data connection to a relational database, either using Tools > Manage Data Connections or File > Add Data Tables... and make the necessary selections until the Views in Connection dialog is shown.
Note: If you want to share the custom query with other people and with other analyses you should set up a shared data connection through Manage Data Connections, rather than adding it directly to the analysis.
In the Views in Connection dialog, select Custom Query > New Custom Query.
Response: The Custom Query dialog is displayed.
Type a Query name.
Comment: This name will also be used as the table name for the resulting table.
Type the Query in the language of the selected database.
Comment: Different databases support different languages. See the documentation for your database for details.
Click on Verify.
Comment: It is recommended to run Verify when the query is done.
Response: The entered query is verified against the database and the Result Columns tab is populated with the columns from the query. If there are any errors in the query you will be informed about this under the Query pane. Click on the Details link for more information. Fix the errors and run Verify again to make sure everything has been taken care of.
Go through the Result columns and make sure that all the desired result columns are listed and that they are of the correct data type.
Comment: You can change the order of which the columns are presented in the resulting Spotfire data table by moving columns up or down in the list.
If any parameters are to be included, follow the instructions under To use parameters in a custom query below.
Click OK.
Response: The custom table based on the custom query is added to the list of Available tables in database and automatically added to the Views in connection list in the Views in Connection dialog.
Comment: You can also choose to combine the new table with other tables by setting up structural relations between the tables.
When you are done with the selection of views to include in the connection, click OK.
To use parameters in a custom query:
Note: When you want to use parameters in a custom query you must always make sure to set up the resulting data table to be loaded on demand. This is necessary in order to define the parameters using one of the available on-demand input methods. You can specify that a data table should be loaded on demand in the Add Data Tables dialog, or in the On-Demand Settings dialog.
Follow the steps above in order to create the custom query.
When you want to call the parameter in the query, specify it with a question mark before the parameter name. For example, SELECT Col1, Col2 FROM Table WHERE Col1 > ?Param1.
On the Parameters tab, make sure that all parameters that you use in the query have been specified.
Comment: Click New... to specify a new parameter and enter a Name, Spotfire data type and a Value type for the parameter.
Click OK to close the Custom Query dialog when done.
Note: To use parameters for personalized queries based on users and groups you need to enable the check box Use parameters for personalized queries (users and groups).
For information on how to edit an already created custom query within your analysis, see To edit a custom query under How to Edit Data Connection Properties.
See also:
Adding Data Connections to an Analysis
How to Work with Data Connections and their Data Sources in the Library