Parameters in custom queries

In a custom query in a data connection, if you want the query to retrieve different results depending on the value of one or more variables, you can set up the query to use parameters. You can map a parameter to, for example, a property control in a text area.

A parameter is referred to using the syntax ?parametername, where parametername is a name that you select for your parameter. You define the data type for the parameter on the Parameters tab of the Custom Query dialog.

Example

SELECT Col1, Col2 FROM Table WHERE Col1 > ?Param1

Note: You cannot use parameters to control what database table to select data from. For example, it is not possible to write a custom query like this:

SELECT * FROM ?parametername

The reason is that a parameter cannot store table identifiers. Strings and table identifiers are typically handled differently in SQL queries.

Adding parameters to a custom query

To use parameters in a custom query, you must call the parameter in the query, and configure the parameters in the Custom Query dialog.

Before you begin

To learn more about creating and writing custom queries, see Custom queries.

Procedure

To create a custom query, follow the steps in Adding a custom query in a data connection.
In the Custom Query dialog, write your query. Where 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, add and specify all the parameters from your query. To add a parameter, click New.
In the New Parameter dialog, enter the information for the parameter; the name, the Spotfire data type, and the data type in the external data source.
To save your changes and close the Custom Query dialog, click OK.

Using custom queries with parameters

Data types and parameters

In many cases, multiple database data types will map to a single Spotfire data type. As an example, the Microsoft SQL Server the data types varchar, nvarchar, text, ntext all map to the Spotfire data type String. In those cases, when you declare the parameter type, you can also specify the data type in the external system. That is, you can have a parameter of the Spotfire data type String that is an nvarchar in the external system. While Spotfire cannot automatically map the external type of the parameter, a manual specification of the external type can make the query more effective. If there is a one-to-one mapping between the Spotfire type and the external type, then the external type drop-down list will be disabled.

Personalized parameters

Provided that you and the end users of the analysis are logged into a Spotfire Server, you can also use one or more of the predefined parameters for personalized queries; current_user, current_groups, and current_user_domain, which you can enable by selecting the Use parameters for personalized queries (users and groups) check box. These parameters work the same way as they do when you use them from the Information Designer, which is described in Personalized information links.

When you have selected the check box, you can use personalized query parameters in your custom query. The type of these parameters is always String in Spotfire and the external type is the default value, for example, NVARCHAR in Microsoft SQL Server.