Details on Custom Query

If you are looking for an introduction to custom queries, information and tips about how you can write custom queries in Spotfire, or instructions on how to add a custom query to your data connection, see:

Details on Custom Query Dialog

This dialog allows you to create your own custom database query. The query results in a custom table which in turn can be used to set up a view in the selected connection in the same way as other database tables. Queries are written in a language that is supported by the target database. For example, for a Microsoft SQL Server database, you would write your custom query in SQL.

Option	Description
Query name	The name of the custom query which will also be used as the table name for the resulting table.
Query	Here you can type or paste a query in a language that the database understands. The query can be parameterized and the used parameters are specified on the Parameters tab.
Verify	Verifies the query and retrieves the result columns which are automatically added to the Result Columns tab. If an error is detected you will get some information about what is wrong under the Query field. Note that the data type entered for a result column must match the actual data type returned from the external system or else the custom query will not work. The verification checks that the metadata returned matches the expected metadata entered in the dialog only. This means that if you make changes in the dialog after a first verification and do not update the result columns, then verification will not find any errors.
Preview	[The preview is supported for connections to external data sources which support the TOP/LIMIT clause only. This is required in order to limit the amount of data returned in the preview.] Allows you to see a preview of the first 100 rows from the resulting table, together with any data table or column properties from the database.

Result Columns Tab

Result columns are those columns, out of the columns returned from your query to the external system, that will be included in the final custom query table. Even if the query you have written selects more columns from the external system, only those columns that are included in the Result columns list will be part of the resulting table.

When you verify the query you have written, all columns returned from the external system are automatically added as result columns in the Result columns list. You can also manually add and remove result columns.

Option	Description
Result columns	Lists all columns that will be included in the resulting custom query table. Note that, even if the query you have written selects more columns from the external system, only those columns that are included in this list will be included of the resulting table.
New...	Opens the New Result Column dialog where you can specify a column to include as a result column.
Edit...	Opens the Edit Result Column dialog where you can edit the configurations for a result column. Note: You cannot change the name of a result column in this dialog.
Delete	Deletes the selected column from the Result column list. The column will not be included in the resulting custom query table.
Move Up	Moves the selected result columns up in the list and, hence, forwards in the order the columns are presented in Spotfire.
Move Down	Moves the selected result columns up in the list and, hence, backwards in the order the columns are presented in Spotfire.

Parameters Tab

Parameterized queries can be used to retrieve different data based on the current user, domain or group. You can also use parameters to control on-demand loading of the data table. When the parameters are used in a query they are prefixed by a question mark.

Option	Description
Parameters	Lists all parameters that can be used in the query.
New...	Opens the New Parameter dialog where you can specify the name, data type and value type of a new parameter.
Edit...	Opens the Edit Parameter dialog where you can change the name, data type and value type of a parameter.
Delete	Deletes the selected parameters from the list.
Use parameters for personalized queries (users and groups)	Select this check box to enable the use of personalized queries based on the predefined parameters current_user, current_groups or current_user_domain. By including these parameters in the query you can set up a data connection to return only information applicable for a certain user, group or domain.