Substitution Parameters

When you define a query on a database, for a data view, chart, graphic report, or as part of any configured process, you can create substitution parameters.

Default substitution parameters are provided. For example, the user can be prompted to select a project name, or given the option to include samples that have already been despatched when selecting samples for a despatch. This may be necessary when a despatch, or samples, needs to be resent.

Use the options provided on the toolbar to manage the items in the list:

	Click Add to add a new item to the list.
	Click the Delete button to remove the item currently selected in the list.
	Click the Move Up button to move the current item up the list.
	Click the Move Down button to move the current item down the list.
	Click the Properties button to view the properties of the currently selected item.
	Click the Copy button to copy the currently selected item in the list.
	Click the Paste button to paste (and add) the copied item to the list.

Identity

When you add or edit a substitution parameter, its properties are defined in the Substitution Parameter Property Editor.

Name

The name of the substitution parameter as it will appear on the Substitution Parameters page of a configured object’s Property Editor.

Description

A brief description of the type of value contained by the parameter and how those values will be used.

Prompt

Prompt String

Enter a string which is used to prompt the user to enter a value when the query is run.

Data Type

Select the type of value the user is required to enter as a parameter. This can be a text string, a numeric or date value, or a list of values separated by commas.

If you are prompting the user for a date, you have the option of including a time portion, so the format may be either Date, Time or DateTime (i.e. date and time). If a date or time is selected, you can select a format from the drop-down format lists .

If you are prompting the user for a list of values, you can specify whether those values are enclosed in quotes.

Format Example

Enter an example of the format that is required to be entered by the user. When you select a date format, an example is automatically shown which matches the date (and time) format you have selected.

Response

Convert text to uppercase

To ensure that the results of the query are not affected by case-sensitivity, you may choose to convert all retrieved text values to UPPERCASE.

Accept blank values without displaying a warning

By default, a warning message is displayed if a blank value is returned in response to a prompt.

In some situations it may be acceptable to allow a blank value to be returned. If this option is selected, a blank value will be accepted and no warning will be displayed.

Default Value

Enter a default value for the substitution parameter. If the data type is a date, click the ellipse button in the input box to display a calendar. Alternatively, enter $DATE to substitute today's date when the query is run.

Use this default as the initial display value

The default value will be displayed the first time you run the query in a session.To display the default value every time you run the query in a session, select the Use this default as the initial display check box option.

Use SQL to generate a pick list

Select this option to enter a SQL query that will be used to retrieve the list of values the user is allowed to enter.

	Click this button to Refresh the results obtained as result of running the query.
	Click this button to open the SQL Editor rather than enter a query directly in the edit box.

Limit user’s response to values in the pick list

If this option is selected, the user can only enter a value that is included in the list of values. If this option is not selected, the user may enter any value.

Lookup Result Column

Select the column that contains the values you want to add to the picklist.

Example

Once you have setup substitution parameters, you can reference them in your queries. Question marks are used to enclose the prompts:

SELECT * FROM G_HEADER

WHERE

 PROJECT = '?Select a project?'

 AND XCOORD >= ?Minimum X for the selection window?

 AND XCOORD <= ?Maximum X for the selection window?

ORDER BY BHID